purge_schema.sql

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;