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
Dig Deeper on Oracle database administration
Related Q&A from Brian Peasland
In this expert answer, Brian Peasland explains how to call a stored procedure inside user-defined functions in Oracle Database. Continue Reading
Learn how to add disk to ASM diskgroup with two-way mirroring and how to specify a failgroup in this tip from Oracle expert Brian Peasland. Continue Reading
Understand how to install the DBCA in Oracle 10g Express Edition and why you cannot simply download DBCA in Oracle in this expert tip. Continue Reading