Home / server / Questions / 382691
Accepted
Brian
Asked: 2012-04-25 10:24:17 +0800 CST

create a 'definition' file from Oracle

  • 772

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

oracle database-administration

2 Answers

  1. Best Answer

    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
    • 3

  2. 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.

    • 0