Home > Schema Cleanup script > How to Delete All Objects for a User in Oracle

How to Delete All Objects for a User in Oracle

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

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: