In our DEV and TST environment we sometimes want to perform a fresh deployment of a database release. Since we got our schemas from the DBA team, but can’t drop/create them ourselves, we found the script below to remove all artifacts from the schema. Packages, tables, sequences, etc. nothing is safe from this armageddon script.

declare stringa varchar2(100);
cursor cur is
select *
from user_objects;
begin
for c in cur loop
begin
stringa := '';
if c.object_type = 'VIEW' then
stringa := 'drop view ' || c.object_name;
EXECUTE immediate stringa;

elsif c.object_type = 'TABLE' then
stringa := 'drop table ' || c.object_name || ' cascade constraints';
EXECUTE immediate stringa;

elsif c.object_type = 'SEQUENCE' then
stringa := 'drop sequence ' || c.object_name;
EXECUTE immediate stringa;

elsif c.object_type = 'PACKAGE' then
stringa := 'drop package ' || c.object_name;
EXECUTE immediate stringa;

elsif c.object_type = 'TRIGGER' then
stringa := 'drop trigger ' || c.object_name;
EXECUTE immediate stringa;

elsif c.object_type = 'PROCEDURE' then
stringa := 'drop procedure ' || c.object_name;
EXECUTE immediate stringa;

elsif c.object_type = 'FUNCTION' then
stringa := 'drop function ' || c.object_name;
EXECUTE immediate stringa;

elsif c.object_type = 'SYNONYM' then
stringa := 'drop synonym ' || c.object_name;
EXECUTE immediate stringa;

elsif c.object_type = 'INDEX' then
stringa := 'drop index ' || c.object_name;
EXECUTE immediate stringa;

elsif c.object_type = 'PACKAGE BODY' then
stringa := 'drop PACKAGE BODY ' || c.object_name;
EXECUTE immediate stringa;

elsif c.object_type = 'DATABASE LINK' then
stringa := 'drop database link ' || c.object_name;
EXECUTE immediate stringa;

elsif c.object_type = 'TYPE' then
stringa := 'drop type ' || c.object_name;
EXECUTE immediate stringa;

end if;

 exception
when others then
null;
end;
end loop;
-- PURGE recyclebin
end;

Way to valuable not to store, so here it is.
All credits go to Massimo @ Oracle OTN Community for his code.

Houd jij je kennis graag up to date?

Mis niets meer van onze kennisdocumenten, events, blogs en cases: ontvang als eerste het laatste nieuws in je inbox!

Fijn dat we je op de hoogte mogen houden!