SQL Script to empty out/cleanse a user schema 20188367001

SQL Script to empty out/cleanse a user schema

OK, it is a fairly trivial task. But when you have to prepare for workshops etc. it may take a lot of time to purge/cleanse/empty all student’s schemas without completely deleting them. Of course deleting them is an option but: it requires DBA privileges and it also removes all privileges granted to the users. What I want to do is simply drop every single object in the user’s schema, without losing the schema altogether. There may be excellent (GUI) tools for this, but my colleague Anton wrote this SQL script (in 1 minute and 44 seconds):

set pages 0
set feedback off

spool del_schema_upx.sql

select 'drop ' || object_type || ' ' || user || '.' || object_name || ';'
from user_objects
where object_type not in ( 'INDEX', 'TRIGGER', 'PACKAGE BODY')

spool off


It does not yet cater for the fact that in 10g dropped objects are not gone but are in the recycle bin instead. They still show up in user_objects – with an unrecognizable name that starts with BIN. To get rid of them for good, you have to purge the recycle bin. The statement shown above will generate statements like: drop TABLE UP5.BIN$WE/4n30QTqaR8/1j4+ZDxw==$0; that will fail because tables in the bin can not be dropped. It also does not cater for the references (foreign keys) that may exist between tables. You may either have to run the script several times or add a statement that will first remove all Foreign Keys. The same applies to dropping of types on which (object) tables or columns in tables (VARRAY, NESTED TABLE) still depend. Finally it tried to drop tables that are created to implement a Materialized View whereas the Materialized View takes the table along with it when dropped.

So with a few adaptions, this is a very useful script – it left me time to write this post.


  1. Pingback: glamour-agency September 29, 2007
  2. bart April 7, 2005
  3. Marco November 10, 2004
  4. andrew November 9, 2004
  5. Marco Gralike November 9, 2004
  6. Marco Gralike November 9, 2004
  7. Lucas November 9, 2004
  8. Lucas November 9, 2004
  9. andrew November 8, 2004