Dropping everything from an Oracle database

Tagged: Oracle

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
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 (@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…..