Friday, June 21, 2013

script to drop all objects in your schema


This script can be used to drop all the objects in your schema,very useful when you want a fresh schema and start from scratch.no need to delete objects individually or recreate user.

-----------------------------------------------------------------------------
-----    plsql script to drop all the objects in your
-----      current schema
--------------------------------------------------------------
declare
v_str1 varchar2(200) := null;
cursor get_sql is
select
'drop '||object_type||' '|| object_name|| DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS PURGE') v_str1
from user_objects
where object_type in ('TABLE','VIEW','PACKAGE','TYPE','PROCEDURE','FUNCTION','TRIGGER','SEQUENCE','SYNONYM')
order by object_type,object_name;
begin
open get_sql;
loop
fetch get_sql into v_str1;
if get_sql%notfound
then exit;
end if;
execute immediate v_str1;
end loop;
close get_sql;
end;
/

2 comments: