Thursday, July 12, 2012

PL/SQL How to: delete all objects in database schema

The simplest way of course is drop and recreate the user. But if you don't have SYSDBA privileges, to delete all objects in the schema you should:

  1. Prepare a series of DROP statements:
    select 'drop '||object_type||' '|| object_name||  DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';') from user_objects
    as a result, you should get:

  2. Copy a list of DROP statements to command window and run it as Script
  3. Cause of possible errors when deleting cascading objects and so on, you may have to empty recycle bin
    purge recyclebin;
    and repeat described procedure once again.