Saturday, March 11, 2017

cleanup the schema like HR Be carefull


 Since I use "CASCADE CONSTRAINTS" for table it doesn't throw me an error.

----------------------------------------------------------------------------

set feedback off
set heading off
set termout off
set linesize 1000
set trimspool on
set verify off
spool c:\temp\drop_objects.lst
SELECT 'DROP ' || OBJECT_TYPE || ' ' || OBJECT_NAME ||';'   FROM USER_OBJECTS
WHERE OBJECT_TYPE <> 'TABLE' AND OBJECT_TYPE <> 'INDEX' AND OBJECT_TYPE<>'PACKAGE BODY' AND OBJECT_TYPE<>'TRIGGER'  AND OBJECT_TYPE<>'LOB'
UNION ALL
SELECT 'DROP ' || OBJECT_TYPE || ' ' || OBJECT_NAME ||' CASCADE CONSTRAINTS;'   FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'TABLE';
spool off;
@c:\temp\drop_objects.lst
prompt 'Purging the recycle bin...'
purge recyclebin;
host del c:\temp\drop_objects.lst

No comments:

Post a Comment

 https://www.linkedin.com/pulse/building-real-time-database-monitoring-dashboard-oracle-khaleeq-tpwxf/