giovedì 6 ottobre 2011

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;

0 Commenti:

Posta un commento

Iscriviti a Commenti sul post [Atom]

<< Home page