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;