Dropping all objects in a schema

Dropping all objects in a schema

If you decide to delete all objects of a certain schema then better drop the schema. It will save timing as well as easy process. However inside a schema a user can manually drop all his objects. Below is the procedure about how a user can drop all object under his schema.

1)Connect to database as the user that you want to drop all his objects.
Suppose I want to drop test schema objects.
$sqlplus test/test

2)Script for dropping all objects.
declare
cursor c_get_objects is
select object_type,'"'||object_name||'"'||decode(object_type,'TABLE' ,' cascade constraints',null) obj_name from user_objects
where object_type in ('TABLE','VIEW','PACKAGE','SEQUENCE','PROCEDURE','FUNCTION',
'SYNONYM', 'MATERIALIZED VIEW')
order by object_type;
cursor c_get_objects_type is select object_type, '"'||object_name||'"' obj_name from user_objects where object_type in ('TYPE');
begin
for object_rec in c_get_objects loop
execute immediate ('drop '||object_rec.object_type||' ' ||object_rec.obj_name);
end loop;
for object_rec in c_get_objects_type loop
begin execute immediate ('drop '||object_rec.object_type||' ' ||object_rec.obj_name);
end;
end loop;
end;
/

3)Purge the recyclebin objects.
SQL>PURGE RECYCLEBIN;

4)Drop the queues if you use advanced queuing.

declare
cursor active_queues is select OBJECT_NAME from user_objects where OBJECT_TYPE = 'QUEUE';
cursor active_queue_tables is select OBJECT_NAME from user_objects where OBJECT_TYPE = 'TABLE';
begin
for v_queue in active_queues loop
DBMS_AQADM.STOP_QUEUE (queue_name => v_queue.object_name);
DBMS_AQADM.DROP_QUEUE (queue_name => v_queue.object_name);
end loop;
for v_table in active_queue_tables loop
DBMS_AQADM.DROP_QUEUE_TABLE (queue_table => v_table.object_name, force => TRUE);
end loop;
end;
/

5)Check for if any objects inside schema.
Be sure no rows return after issuing following query.

SQL> select * from user_objects;
no rows selected