Drop all objects from an Oracle schema

One of the projects that I have been working on recently supports using an Oracle database to persist data. In our development environment, we've setup an Oracle database instance with which individual developers and testers can utilize for their work.

One of the things that I find myself frequently needing to do is rebuild the project's database schema. As we are in active development, the objects are frequently being added, modified, and\or removed. In order to ensure that I am starting from a clean slate, I have typically dropped and recreated the database user account. However, this has a significant drawback that a second account must be used to drop and recreate the user and that account must have increased permissions in order to drop and create other users.

To solve this problem, I created a SQL*Plus script that uses the Oracle catalog tables to dynamically construct and execute a script which drops all of the user's objects.

The script has the following high -level outline:

1. Sets SQL*Plus parameters to enable the output of the script to be run as a script itself
2. Remove the contents of the user's Oracle recycle bin
3. Start spooling the output of the SQL*plus session to a file which will be executed at the end of the script
4. Using the USER_CONSTRAINTS table, execute a select statement that creates disable constraint statements for all the user's constraints.
5. Using the USER_OBJECTS table, execute a select statement that creates drop statements for all of the user's objects.
6. End spooling the output of the SQL*Plus session
7. Execute the spooled file as a script to drop the objects.

Below is the actual script:

SET HEADING OFF;
SET FEEDBACK OFF;
SET ECHO OFF;
SET SQLPROMPT "--";

PURGE RECYCLEBIN;

SPOOL DropAllObjects.out;

SELECT 'ALTER TABLE ' || TABLE_NAME ||
       ' DISABLE CONSTRAINT ' || CONSTRAINT_NAME || ';'
FROM   USER_CONSTRAINTS
ORDER BY TABLE_NAME ASC;

SELECT 'DROP ' || object_type || ' ' ||
       object_name ||
       DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';')
from   user_objects
ORDER BY OBJECT_TYPE ASC, OBJECT_NAME ASC;

SPOOL OFF;

START DropAllObjects.out;

EXIT;

Notes:

1. While running the script it is possible to receive object does not exist errors. This is perfectly normal as the objects may have been dropped by earlier statements in the script do to cascade being used. Running the script a second time should result in no errors, as all of the objects should have been removed.
2. Our project doesn't use Oracle user functions or packages so the script hasn't been extensively tested against schema which use them

Welcome!

So, I finally decided to start a public blog after putting it off for some time. Please feel free to leave comments, suggestions, corrections, or improvements regarding anything I may post about.

Thanks,
Matt