Posted on Leave a comment

Drop all objects in schema

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.

If you don’t have system level access, and want to scrub your schema, the following sql will produce a series of drop statments, which can then be executed. Note: be sure you are connected as the schema owner, would be tragic to drop system tables….

spool dropall.sql
select ‘drop ‘||object_type||’ ‘|| object_name|| DECODE(OBJECT_TYPE,’TABLE’,’ CASCADE CONSTRAINTS;’,’;’)
from user_objects
spool off

Then, can purge the recycle bin to really clean things up:

purge recyclebin;

This will produce a list of drop statements. Not all of them will execute – if you drop with cascade, dropping the PK_* indices will fail. But in the end, you will have a pretty clean schema. Confirm with:

select * from user_objects

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.