Oracle

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
  1. Run the following command:
@empty_db.sql
  1. You will see the file dropObjects.sql has been created, now execute the following command:
@dropObjects.sql
  1. 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:

Read next