SQL stored procedures delete all object statements of a user

For an SQL statement that may be encountered during development, we use the SQL stored procedure to delete all objects under the user. If you need it, you can refer to it. It is very simple.

For an SQL statement that may be encountered during development, we use the SQL stored procedure to delete all objects under the user. If you need it, you can refer to it. It is very simple.

The Code is as follows:

Create or replace procedure drop_all as cursor cur_obj is Uo. OBJECT_NAME, uo. OBJECT_TYPE From user_objects uo Where uo. OBJECT_NAME not in ('drop _ all ') And uo. OBJECT_TYPE not in ('lob '); /* Cursor cur_tablespace is Select ut. TABLESPACE_NAME From user_tablespaces ut Where ut. TABLESPACE_NAME not in ('System', 'sysaux ', 'undotbs1', 'temp', 'users ');*/ V_obj_name user_objects.OBJECT_NAME % type; V_obj_type user_objects.OBJECT_TYPE % type; /* V_tablespaces_name user_tablespaces.TABLESPACE_NAME % type ;*/ SQL _str1 varchar2 (2000 ); /* SQL _str2 varchar2 (2000 );*/ Begin Open cur_obj; Loop Fetch cur_obj Into v_obj_name, v_obj_type; Exit when cur_obj % notfound; SQL _str1: = 'drop' | v_obj_type | ''| v_obj_name; Execute immediate SQL _str1; End loop; Close cur_obj; /* Open cur_tablespace; Loop Fetch cur_tablespace Into v_tablespaces_name; Exit when cur_tablespace % notfound; SQL _str2: = 'drop tablespace' | v_tablespaces_name | 'Encuding CONTENTS '; Execute immediate SQL _str2; End loop; Close cur_tablespace ;*/ End drop_all;

This stored procedure can delete almost all objects under the user. Release the items in the comment to delete the tablespace. This process cannot be rolled back and never used in the production environment or useful environment. I am not responsible for the results of this process.