SET heading OFF

SET feedback OFF

SET echo OFF

SET linesize 300

SET pagesize 0

SET sqlprompt ">>"

PURGE RECYCLEBIN;

spool dropall_objects.SQL

SELECT 'SPOOL DropAll_objects.lst' FROM dual;

SELECT 'ALTER TABLE ' || table_name ||        ' DISABLE CONSTRAINT ' || constraint_name || ';'
FROM   user_constraints
WHERE  USER IN ( 'YOUR_DATABASE_USERNAME')
AND EXISTS ( SELECT vi.instance_name FROM v$instance vi WHERE vi.instance_name = 'xe')
AND table_name NOT LIKE ('BIN$%')
ORDER BY table_name ASC;

SELECT 'DROP ' || object_type || ' ' ||
 object_name ||
DECODE(object_type,'TABLE',' CASCADE CONSTRAINTS;',';')
FROM   user_objects
 WHERE  USER IN ( 'YOUR_DATABASE_USERNAME')
AND EXISTS ( SELECT vi.instance_name FROM v$instance vi WHERE vi.instance_name = 'xe')
AND object_type NOT IN( 'PACKAGE BODY','DATABASE LINK')
AND object_name NOT LIKE ('BIN$%')
ORDER BY object_type ASC, object_name ASC;
SELECT 'spool off'  FROM dual
EXIT;
spool off

 

The script assume your db instance is ‘xe’

Advertisements