Someitmes you wish to have a 'clean' database, or you may not wish to create your database from scratch, then in this case you can delete all the objects from your database as follows using SQLPlus:
You can use toad, using the results from the select statements between the spool lines below.
NOTE: USE WITH GREAT CAUTION!
1. Put the following in the file “DB_drop.sql”
set feedback off
set pagesize 0
spool AllObjectsDrop.sql
select 'drop view '||view_name||';' from user_views;
select distinct 'drop sequence '||sequence_name|| ';'from user_sequences;
select distinct 'drop table '||table_name|| ';'from user_tables;
select distinct 'drop procedure '||name|| ';'from user_source where type = 'procedure';
select distinct 'drop function '||name|| ';'from user_source where type = 'function';
select distinct 'drop package '||name|| ';'from user_source where type = 'package';
select 'drop synonym '||synonym_name||';' from user_synonyms where synonym_name not like 'sta%' and synonym_name like 's_%'
spool off
2. Log into the database as the user for the database you want to drop
3. Run the script (@DB_drop.sql). This creates the file “AllObjectsDrop.sql” which contains commands to drop all objects.
4. Run the created script (@AllObjectsDrop.sql)
