Dropping all User Objects Example

This is one in the list of my Example Series in this blog. Check this blog for more possible examples

-- A lot of times I want to drop all user objects in the schema. I use the following script

-- remember to connect first as the user/owner of the objects you want to drop. Do NOT run this as another user or SYS

declare
del_objs integer;
obj_del integer;
cursor get_objs is
select object_type,
'"'||object_name||'"'||decode(object_type,'TABLE' ,' cascade constraints',null) obj_nam
from user_objects
where object_type in ('TABLE',
'VIEW',
'PACKAGE',
'SEQUENCE',
'PROCEDURE',
'FUNCTION',
'SYNONYM', 'MATERIALIZED VIEW')
order by object_type;
cursor get_objs2 is
select object_type, '"'||object_name||'"' obj_nam
from user_objects
where object_type in ('TYPE');
begin
for objrec in get_objs loop
execute immediate ('drop '||objrec.object_type||' ' ||objrec.obj_nam);
end loop;

for objrec in get_objs2 loop
begin
execute immediate ('drop '||objrec.object_type||' ' ||objrec.obj_nam);
exception
when others then
null;
end;
end loop;
end;
/

-- Remember to drop the Queues if you are using AQ
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 next_queue in active_queues loop
DBMS_AQADM.STOP_QUEUE (queue_name => next_queue.object_name);
DBMS_AQADM.DROP_QUEUE (queue_name => next_queue.object_name);
end loop;

for next_table in active_queue_tables loop
DBMS_AQADM.DROP_QUEUE_TABLE (queue_table => next_table.object_name, force => TRUE);
end loop;
end;
/

-- Remember to Purge the Recylebin if you are using 10g

purge recyclebin
/

-- Comfirm all is dropped

select * from user_objects
/