Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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!)

share|improve this question
up vote 3 down vote accepted

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
share|improve this answer
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: as well as and

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

Also, there's a site which may help you better.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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