--
-- an attempt at a script to drop all items in one's Oracle account
-- please note: it currently only handles tables, views, sequences,
--              triggers, and procedures. 
-- also please note: it leaves a script drop_all_temp.sql in the 
--              directory from which you are running sqlplus
--
-- by: Sharon M. Tuttle
-- last modified: 1-30-08 - added "purge recyclebin;" (thanks to A. Neyman!)
--                9-12-02

set echo off
set feedback off
set heading off
set pagesize 0
set newpage 0
set space 0
set linesize 80
clear breaks
clear columns
clear computes

-- build a script containing commands to drop all
-- current tables, views, sequences, triggers, and 
-- procedures
spool  drop_all_temp.sql

select 	'drop table ' || table_name || ' cascade constraints;'
from   	user_tables;

select 	'drop view ' || view_name || ';'
from   	user_views;

select 	'drop sequence ' || object_name || ';'
from	user_objects
where	object_type = 'SEQUENCE';

select  'drop procedure ' || object_name || ';'
from	user_objects
where 	object_type = 'PROCEDURE';

select	'drop trigger ' || object_name || ';'
from	user_objects
where   object_type = 'TRIGGER';

select  'drop package ' || object_name || ';'
from    user_objects
where   object_type = 'PACKAGE';

select  'drop function ' || object_name || ';'
from    user_objects
where   object_type = 'FUNCTION';

-- need ;; below to get one ; printed in prompt's result!

prompt purge recyclebin;;

spool off

-- now, run the drop-script you have created above

@ drop_all_temp

clear breaks 
clear columns
clear computes

set feedback 6
set pagesize 14
set linesize 80
set newpage 1

prompt these still have not been removed:
prompt **********************************
column object_name heading 'object name' format a20 
column object_type heading 'object type' format a20
select 		object_name, object_type
from   		user_objects
order by 	object_type, object_name;