Schema Cleanup script – Oracle

Posted by
November 22, 2008
  

Working in Oracle, many a times a complete schema cleanup is required due to certain reasons e.g. applying imports from other databases etc.
Normally, it is simplest to drop and add the user. This is the preferred method if you have system or sysdba access to the database. But if one doesn’t have these accesses, a script can be created that picks up all objects from user_objects table and generates drop statements for all of them. Following statement can be used to view the objects in a schema:

 
SELECT * FROM user_objects
 

If you don’t have system level access, and want to scrub your schema, the following sql will produce a series of drop statements, which can then be executed. Not all of them will execute – if you drop with cascade, dropping the PK_* indices will fail:

 
SELECT 'drop '||object_type||' '|| object_name||  DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';')
FROM user_objects
 

It’s best to purge the recycle bin to finally clean things up. This can be done using the following statement:

 
purge recyclebin;
 

Above steps do cleanup the schema, but do not really work well in certain cases e.g. tables are indexed or have referential integrity or collections are being used. The reason is that the above sql doesn’t return drop statements in order of dependency. One way of doing this is ordering by ‘object_id’ in descending order. This will ensure that the objects will be dropped in reverse order of creation e.g. A table Employee is created and is assigned object_id 100. Then an index Employee_Idx is created on this table, this will be assigned an object_id greater than that of Employee table, say 101. Now when the above query is ordered by object_id in descending order, the index will be dropped first followed by the table. Hence, all the dependent objects will be dropped before their parent objects.

The following script sums up above mentioned steps and can be executed to cleanup a complete schema. Save it in a file and execute the file on sqlplus:

 
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
/
 

Most Commented Posts

database, interview, oracle, problem, question

If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.

Comments

No comments yet.

Leave a comment

(required)

(required)