How to empty an Oracle 11g XE database

Once I needed to empty an Oracle database that was used by several systems, so it was a big mess with tables not needed and a lot of trash. Anyway, here’s a script that will generate a file with drop commands, this is the one that will drop all objects in the database. The good thing about this is that you can choose to drop one type objects only, say tables or triggers, etc.

Here is how to do it:

  1. In the command window move to a directory in which you can create a file
  2. Use sqlplus to login into the database you want to empty
  3. Type the following lines in a new file and save it as empty_db.sql
    set feedback off
    set pagesize 0
    spool dropObjects.sql
    select 'drop view '||view_name||';' from user_views;
    select 'drop index '||index_name||';' from user_indexes;
    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 distinct 'drop type '||name|| ';'from user_source where type = 'TYPE';
    select distinct 'drop trigger '||name|| ';'from user_source where type = 'TRIGGER';
    select 'drop synonym '||synonym_name||';' from user_synonyms;
    spool off
  4. Run the following command:
    @empty_db.sql
  5. You will see the file dropObjects.sql has been created, now execute the following command:
    @dropObjects.sql
  6. You will see that all objects in the file are dropped from the database

Sometimes you could get errors, specially if you are using an old database which you are not familiar with. In my case I had a few but where not relevant so I didn’t care much. Anyway I hope you find this useful.

Source

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: