Dropping everything from an Oracle database

Posted by u02sgb in 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…..

The Conversation

Follow the reactions below and share your own thoughts.

2 Responses to “Dropping everything from an Oracle database”

  1. December 22, 2008 at 8:33 pm, alex said:

    or you can drop specific users and all it’s objects using:

    DROP USER CASCADE

    Reply

  2. September 04, 2009 at 2:40 am, Anonymous said:

    I’m loving this..I am creating a database for an application called POSSE..Everytime i miss out a step or an error occur,i’ll need to do that back again(i mean delete the DB and create a new 1)..now diz just helped me..nice work mate..cheers

    Reply

Leave a Reply