How to: I need an empty Database! – Part 1

In your daily work, for various reasons you may need an empty database.  May be for testing purposes, for testing bulk data load or to identify changes over tables caused by an application that is working over the database.

Whichever the case may be, you need a clean database and not always got the time (or the option) to start following relational constraints backwards.

Here’s an option to get an empty database out from a fully working online one.

Import / Export  Utilities (Oracle only)

For the task at hand, using the IMP/EXP utilities can be quite helpful and fast. Both of them provide the option to import/export the full database, only a schema and with or without data.

If you don’t have any .DMP file or just want to create a new one, you can use the following command to export the database DDL.

EXP SYSTEM/mypswd FILE=fulldb.dmp FULL=Y ROWS=N

Or as well you can make a parameters file containing this information

FILE=fulldb.dmp
FULL=Y
ROWS=N
LOG=export.log

and then use it as a parameter itself as follows

EXP SYSTEM/mypswd PARFILE=myparams.dat

Now, if you do have a DMP generated, let’s say, by a backup policy, then you can just use the IMP utility to import the DDL mostly the same way as before with just some little changes.

Let’s state that when someone says “I need an empty database” it really means (usually) “I need an empty schema”

So, you must specify (to avoid loosing data dictionary and other important data) the parameters FROMUSER and TOUSER.  This will tell the utility which schema we want to import, as follows

IMP SYSTEM/mypswd FILE=fulldb.dmp FROMUSER=my_schema TOUSER=another_schema

In the next part, I’ll post some procedures that can be used to clean any schema of the database.  For Oracle, they can be packaged, for other DBMS you just can run the stored procedures.

As always, hope this helps…  And comments are welcome!