The code lives as a gist over on github. A snapshot is below, for simplicity:
set echo off
set verify off
set serveroutput on size 100000
-- Hosted at http://lastinfinitetentacle.blogspot.com/2012/03/empty-oracle-schema-leave-empty-schema.html
-- Disable all contraints
BEGIN
FOR c IN
(SELECT c.owner, c.table_name, c.constraint_name
FROM user_constraints c, user_tables t
WHERE c.table_name = t.table_name
AND c.status = 'ENABLED'
ORDER BY c.constraint_type DESC)
LOOP
dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' disable constraint ' || c.constraint_name);
END LOOP;
END;
/
-- remove all objects
declare
cursor dropObjectsCusor is
select 'drop ' || object_type || ' ' || object_name as sqlDropStmt
from user_objects
where object_type <> 'TABLE' and object_type <> 'INDEX'
order by object_type;
cursor dropTablesCusor is
select 'truncate table ' || object_name as sqlTruncTbl,
'drop table ' || object_name || ' cascade constraints' as sqlDropTbl
from user_objects
where object_type = 'TABLE'
order by object_type;
begin
for ob in dropTablesCusor
loop
begin
execute immediate ob.sqlTruncTbl;
exception when others then dbms_output.put_line('Could not truncate a table.');
end;
begin
execute immediate ob.sqlDropTbl;
exception when others then dbms_output.put_line('Could not drop a table.');
end;
end loop;
for ob in dropObjectsCusor
loop
begin
execute immediate ob.sqlDropStmt;
exception when others then dbms_output.put_line('Could not drop some object.');
end;
end loop;
end;
/
purge recyclebin;
