Saturday 18 August 2012

GENERATE SCRIPT TO CLEAN THE OBJECTS IN A SCHEMA.


set heading off;
set feedback off;
SELECT 'DROP '||object_type||' '|| object_name||  DECODE(object_type,'TABLE',' CASCADE CONSTRAINTS;',';') FROM user_objects ORDER BY object_id DESC;

SELECT 'PURGE RECYCLEBIN;' FROM dual;
SELECT 'PROMPT After cleanup, number of objects remaining in schema' FROM dual;
SELECT 'SELECT COUNT(*) FROM user_objects;' FROM dual;

LOGIN SYSTEM or SYS :

SELECT 'DROP '||object_type||' '|| object_name||  DECODE(object_type,'TABLE',' CASCADE CONSTRAINTS;',';') FROM dba_objects where owner='SCOTT' ORDER BY object_id DESC;

NOTE : Here owner= 'SCOTT' , You can change schema name which you want to clean.

No comments:

Post a Comment