Delete all objects from a Schema

Useful when doing an Import so you don't have to recreate the user, grants, db links, etc.

set echo off;
set feedback off;
set heading off;
set verify off;
set linesize 300;

accept schemaName prompt "Enter Schema Name: ";

select to_char(sysdate, 'MM-DD-YYYY HH:MM:SS') from dual;

prompt Disabling Constraints. Logged to disable.constraints.log...;

set termout off;

spool _disable.constraints.sql;

select 'spool disable.constraints.log append;' from dual;

select 'alter table ' || '&schemaName' || '.' || c.table_name || 
' disable constraint ' ||  constraint_name || ' cascade;'
   from dba_constraints c, dba_tables t
   where c.table_name = t.table_name
     and t.owner = upper('&schemaName')
    order by t.table_name,
             constraint_name;

select 'spool off;' from dual;

spool off;

@_disable.constraints.sql;

set termout on;

select to_char(sysdate, 'MM-DD-YYYY HH:MM:SS') from dual;

prompt Constraints Disabled.;
prompt Dropping all objects for Schema &schemaName.. Logged to drop.schema.objects.log...;

set termout off;

spool _drop.schema.objects.sql;

select 'spool drop.schema.objects.log append;' from dual;

select 'drop table ' || '&schemaName' || '.' || table_name || 
' cascade constraints;' 
    from dba_tables where owner = upper('&schemaName')
    order by table_name;

select 'drop ' || object_type || ' ' || '&schemaName' || '.' || 
object_name || ';'
    from dba_objects
    where owner = upper('&schemaName')
      and object_type NOT IN ('PACKAGE BODY', 'UNKNOWN', 'DATABASE LINK', 
'TABLE', 'INDEX', 'TRIGGER', 'JOB', 'LOB')
    order by object_type,
             object_name;

select 'spool off;' from dual;

spool off

@_drop.schema.objects.sql

set termout on;

select to_char(sysdate, 'MM-DD-YYYY HH:MM:SS') from dual;

prompt Objects dropped. Check drop.schema.objects.log for errors.;

select to_char(sysdate, 'MM-DD-YYYY HH:MM:SS') from dual;

prompt Purging Recycle Bin...

purge dba_recyclebin;

select to_char(sysdate, 'MM-DD-YYYY HH:MM:SS') from dual;

prompt Script Complete.