Q

Copy Oracle Database but with no data

One reader asks how he can make a copy of his Oracle Database but not have any data in it, for test purposes.

I desperately need to make an exact copy of my current (and rather large) Oracle database. All that I need is a copy containing all the tables, with their appropriate fields and relationships. I do not need the data, only a test database to, well test new ideas on.

You can use export/import or Data Pump to do the trick. You'll end up exporting the database objects without any rows of data and then importing the empty tables into your destination.

To do this with export/import, use the following to move one schema:

exp file=my_dump.dmp owner=schema_name rows=n

The ROWS=N parameter tells Oracle not to export any data. Then just import at your destination:

imp file=my_dump.dmp fromuser=schema_name

You can accomplish the same thing with the newer Data Pump utilities similar to the following:

expdp directory=data_pump_dir dumpfile=my_dump.dmp schemas=schema_name content=metadata_only

impdp directory=data_pump_dir dumpfile=my_dump.dmp schemas=schema_name

This was first published in July 2011

Dig deeper on Oracle database backup and recovery

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close