Archive for the ‘Schema Cleanup script’ Category
How to Delete All Objects for a User in Oracle
March 3, 2010
Leave a comment
If you don’t have system level access(Sys or System), and want to clean your schema, the following sql will produce a series of drop statments, which can then be executed.
select 'drop '||object_type||' '|| object_name|| DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';') from user_objects
Then, I normally purge the recycle bin to really clean things up. To be honest, I don’t see a lot of use for oracle’s recycle bin, and wish i could disable it… but anyway:
purge recyclebin;
Following Script can run the full System
SET feedback off SET verify off SET echo off PROMPT Number of objects IN the schema: SELECT COUNT(*) FROM user_objects; PROMPT Finding objects TO DROP SET termout OFF SET pages 80 SET heading OFF SET linesize 120 PURGE RECYCLEBIN; SPOOL c:\cleanup_schema.sql SELECT 'SPOOL c:\cleanup_schema.log' FROM dual; 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; SELECT 'SPOOL OFF' FROM dual; SELECT 'EXIT;' FROM dual; SPOOL OFF SET termout ON PROMPT Dropping Objects now ... -- Execute the sql file created earlier --@c:\cleanup_schema.sql EXIT /Reff: http://forums.oracle.com/forums/message.jspa?messageID=1057359
Categories: Schema Cleanup script
Schema Cleanup script