deleting all objects of a schema

The process for deleting all objects can be summarized by

remove all constraints on tables
delete all tables, indexes, sequences, procedures, etc.
purge the recyclebin

The only practical way of doing the first two above is to take the process in two stages:
Stage 1. The first stage selects from the USER_OBJECTS table along with strings that will later be used as SQL:

select 'drop '||object_type||object_name||';' from USER_OBJECTS;

row returned, example-> drop TABLE SAKAI_USER;

Stage 2. the second stage is to run the script generated from stage 1

The method for doing the varies a little based on the client used. There is room below for adding scripts for this for various clients.

Finally, the command 'purge recyclebin' will remove all the cached objects (eg, tables with names like 'BIN$%')

Scripts

henplus

HenPlus is a java based commandline client with a functional base which is based on that of SQLPlus. But, it is a big improvement on it.

Here is a script for henplus:

You must run this from the same directory that the script is in (ok, henplus needs a little more improvement )
Also, notice that this script was orginally written for sqlplus and all the original lines are still in the script but are commented out


-- #############################################################################################
--
-- %Purpose: Drop all objects of the user that executes this script.
--
-- #############################################################################################
--
-- Akadia SQL Utility Scripts
--
-- Requires Oracle 8.1
--
------------------------------------------------------------------------------

PROMPT
PROMPT Generating script to drop the objects...

--set pagesize 0
--set feedback off
set-property sql-result-showfooter off
set-property sql-result-showheader off
set-property column-delimiter  ''
--set termout off
--set linesize 100
--set trimspool on
--set wrap on

spool drop_user_objects.lst.sql

PROMPT PROMPT
PROMPT PROMPT Dropping public synonyms...

SELECT 'PROMPT ... dropping public synonym '||synonym_name
,      'drop public synonym '||synonym_name||';'
FROM   all_synonyms
WHERE  table_owner = ( SELECT user
                       FROM   dual
                     )
/

PROMPT PROMPT
PROMPT PROMPT Dropping relational constraints...

SELECT 'alter table '||table_name||' drop constraint '||constraint_name||';'
FROM   user_constraints
WHERE  constraint_type = 'R'
/

PROMPT PROMPT
PROMPT PROMPT Dropping remaining user objects...

select  'drop '||object_type||' '||object_name||';'
from   user_objects
WHERE  object_type != 'INDEX' 
   and object_type != 'LOB' and object_name not like 'BIN$%'
/

spool off
--set feedback on
--set termout on
spool drop_user_objects.log
start drop_user_objects.lst.sql

PROMPT
PROMPT All database objects of the user dropped.
PROMPT Please review the log file drop_user_objects.log in the current directory.
PROMPT
PROMPT Count of remaining objects:

--set feedback off

SELECT count(*) REMAINING_USER_OBJECTS
FROM   user_objects
/

--set feedback on
spool off
set-property sql-result-showfooter off
set-property sql-result-showheader off
reset-property column-delimiter