Wednesday, June 29, 2011

Script to Purge All Objects for a User in Oracle

I have to purge and reload an Oracle Database dump on a monthly basis. I have been manually deleting all of the Objects with Toad before loading the new dump. I searched for a script that would delete all of the objects. Many people suggested that you should just drop the user, but if I do this I have to add back in grants that don't get picked up by the export.

This thread on the Oracle Discussion Forum has several solutions. I am pasting the script that worked the best for me below from Massimo. There is some criticism in the thread about why he did not just use the object name in one case instead of all the If/Else conditions; however, I like this approach better, because I can easily take out something if I need to:

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;
end if;
exception
when others then
null;
end;
end loop;
-- PURGE recyclebin

end;
/

No comments: