Advertisements
RSS

Delete all artifacts in an Oracle database schema

29 Aug

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.

Advertisements
 
Leave a comment

Posted by on 29-08-2013 in Database, Oracle

 

Tags: ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: