I have inherited an oracle database, and have no Oracle experience. I have been tasked with building a 'clone' of the database on a new server. I am searching around, but not finding the things I am looking for, and perhaps my terminology is wrong.

Is there a way, in an oracle database, to run a command, and end up with a large SQL file that can be run on a new server, in order to create all the tables, functions, stored procedures, etc. I don't want any actual data, just the structure of the database. This will be used for a new customer, and we definitely don't want to share the previous customers data.

This DB has been in use for several years, and has hundreds of tables, stored procedures, etc, with no kind of centralized control, so i need to pull it from the running DB. (and archive it away in source control!)


Unless your oracle version is 9 or below you really want to avoid exp or imp, they're less flexible and less powerful than the replacements expdp and impdp.

I'm assuming you want to export just a schema. If you want to export a whole database instance (not advisable), i.e. with all the sys/system/etc. schemas and tablespaces, use full=y and no schema statament.

To accomplish what you ask with expdp do something like:

expdp dumpfile=example.dmp log=example.log content=metadata_only schemas=example_schema

To get a text file with sql statements you would do the following using the above created dumpfile:

impdp dumpfile=example.dmp sqlfile=example.sql
  • so I could run those two commands (probably take a while) and end up with a large example.sql text file.. I could then clean that up a bit, then import it into my source control, and then later, see what tables and such have changed? – Brian Apr 24 '12 at 19:36
  • Yes, it probably won't take that long. Exporting generally is faster than importing, and exporting metadata only is faster still and creating an sqlfile is even faster. To give an example, here an ~80 GB dumpfile takes a few minutes to generate an sqlfile, but a few hours to import fully. And to generate an export for such a schema takes about half an hour. But that's on a raid10, with 8 cores and 32 GB RAM. – aseq Apr 24 '12 at 20:21

The command you want will look something like:

exp username/password file=emp.dmp log=emp.log rows=no

Here's a FAQ on exp/imp: http://www.orafaq.com/wiki/Import_Export_FAQ as well as http://www.orafaq.com/wiki/Export and http://www.orafaq.com/wiki/Import

Note that exp generates a binary file, not a text file with SQL statements like, say, mysqldump.

Also, there's a http://dba.stackexchange.com site which may help you better.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.