Dropping everything from an Oracle database
If you don’t want to create your database from scratch it’s sometimes useful to just delete all the objects from it.
1. Put the following in the file “DBdrop.sql”
set feedback off
set pagesize 0
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_%'
2. Log into the database as the user for the database you want to drop
3. Run the script (@DBdrop.sql). This creates the file “AllObjectsDrop.sql” which contains commands to drop all objects.
4. Run the created script (@AllObjectsDrop.sql)
Watch in growing panic because you didn’t mean to press return :). Oh, and I’m not responsible blah, blah…..
Follow the reactions below and share your own thoughts.