quick script to drop all objects in an Oracle 10 Schema
begin
for i in (select * from user_objects where object_type in ('TABLE')) loop
dbms_output.put_line('drop '||i.object_type||' '||i.object_name || ' CASCADE CONSTRAINTS') ;
execute immediate 'drop '||i.object_type||' '||i.object_name || ' CASCADE CONSTRAINTS';
end loop;
for i in (select * from user_objects where object_type in ('VIEW','SEQUENCE','DATABASE LINK','PACKAGE','PACKAGE BODY', 'TRIGGER', 'FUNCTION','PROCEDURE', 'SYNONYM')) loop
dbms_output.put_line('drop '||i.object_type||' '||i.object_name ) ;
execute immediate 'drop '||i.object_type||' '||i.object_name ;
end loop;
execute immediate 'purge recyclebin' ;
end;
for i in (select * from user_objects where object_type in ('TABLE')) loop
dbms_output.put_line('drop '||i.object_type||' '||i.object_name || ' CASCADE CONSTRAINTS') ;
execute immediate 'drop '||i.object_type||' '||i.object_name || ' CASCADE CONSTRAINTS';
end loop;
for i in (select * from user_objects where object_type in ('VIEW','SEQUENCE','DATABASE LINK','PACKAGE','PACKAGE BODY', 'TRIGGER', 'FUNCTION','PROCEDURE', 'SYNONYM')) loop
dbms_output.put_line('drop '||i.object_type||' '||i.object_name ) ;
execute immediate 'drop '||i.object_type||' '||i.object_name ;
end loop;
execute immediate 'purge recyclebin' ;
end;
