How to delete all objects owned by a user in Oracle

In Oracle, how does the following script map all objects owned by a user, tables, sequence, and procedure... Delete all.

 

1. Drop object method. Delete all foreign key constraints first.

 

Declare

Type cst_table_list is table of varchar2 (40 );
Type cst_list is table of varchar2 (40 );

Type name_list is table of varchar2 (40 );
Type type_list is table of varchar2 (20 );

Cst_tab_name cst_table_list: = cst_table_list ();
Cst_name cst_list: = cst_list ();

Tab_name name_list: = name_list ();
Tab_type type_list: = type_list ();

SQL _str varchar2 (500 );

V_err_code number;
V_err_msg varchar2 (200 );

Begin
-- Drop all reference constraints begin
SQL _str: = 'select table_name, constraint_name from user_constraints where constraint_type = 'r ''';
Execute immediate SQL _str bulk collect into cst_tab_name, cst_name;
For I in cst_tab_name.first... cst_tab_name.last Loop

Begin
SQL _str: = 'alter table' | cst_tab_name (I) | 'drop constraint' | cst_name (I );
Execute immediate SQL _str;
Dbms_output.put_line (SQL _str );

Exception
When others then
V_err_code: = sqlcode;
V_err_msg: = substr (sqlerrm, 1,200 );
Dbms_output.put_line ('error code: '| v_err_code );
Dbms_output.put_line ('error message: '| v_err_msg );

End;

End loop;

-- Drop all reference constraints end

-- Drop all tables, sequences... begin
SQL _str: = 'select uo. object_name, UO. object_type
From user_objects UO
Where uo. object_type not in (''index'', ''lob '')
Order by uo. object_type DESC ';
Execute immediate SQL _str bulk collect into tab_name, tab_type;

For I in tab_name.first .. tab_name.last Loop
Begin
SQL _str: = 'drop' | tab_type (I) | ''| tab_name (I );
Execute immediate SQL _str;
Dbms_output.put_line (SQL _str );

Exception
When others then
V_err_code: = sqlcode;
V_err_msg: = substr (sqlerrm, 1,200 );
Dbms_output.put_line ('error code: '| v_err_code );
Dbms_output.put_line ('error message: '| v_err_msg );
End;

End loop;
-- Drop all tables, sequences... end
End;

 

2. delete a user. However, you must have the permission to drop the user and create a dedicated tablespace for the user.

Drop User Username cascade;
Drop tablespace username_tablespace including contents and datafiles;