What's the best way to recreate an Oracle database?

Oracle 11gR2 (x86 Windows):

I have a db with 250 tables with indexes and constraints. I need to re-create these tables, indexes and constraints in a new db and load the data. I need to know how to do the following in SQL Plus and / or SQL Developer, unless there is a magic utility out there that could automate the whole thing. Thanks in advance!

  • Unload (export) all data from 250 tables.

  • Create a sql script file containing instructions CREATE TABLE

    for 250 tables.

  • Create a sql script file containing instructions CREATE INDEX

    for 250 tables.

  • Create a sql script file containing instructions ALTER TABLE ADD CONSTRAINT

    for 250 tables.

  • Run script to create tables in new db.

  • Load the exported data into tables in a new db.

  • Run the script to create all indexes.

  • Run the script to add all constraints.

EDIT: I'm connected to a remote desktop that links to the original db on Windows Server 2008. The remote client only has an Oracle client. For security reasons, I am not allowed to directly link from my local machine to Win Server, so that I can dump the entire source db to the remote machine and then pin it to the local target machine? I am trying to replicate an entire db to my computer.


source to share

3 answers

Starting with Oracle 10g, you can use Data Pump command-line clients expdb

and impdb

to export / import data and / or schema from one database to another. In fact, these two command line utilities are just wrappers that "use the procedures provided in the PL / SQL package DBMS_DATAPUMP to execute export and import commands using the parameters entered on the command line." (quoted in Oracle documentation)

Considering your needs, you need to create a directory and then generate a complete dump of your database with expdb


SQL> CREATE OR REPLACE DIRECTORY dump_dir AS '/path/to/dump/folder/';


sh$ expdp system@db10g full=Y directory=DUMP_DIR dumpfile=db.dmp logfile=db.log


Since the dump is written using some binary format, you will have to use the appropriate import utility to (re) import your DB. Basically replacing expdb

with impdb

in the above command:

sh$ impdp system@db10g full=Y directory=DUMP_DIR dumpfile=db.dmp logfile=db.log


For a simple table dump, use this version:

sh$ expdp sylvain@db10g tables=DEPT,EMP directory=DUMP_DIR dumpfile=db.dmp logfile=db.log


As you noticed, you can use it with your standard user account if you have access to the given directory ( GRANT READ, WRITE ON DIRECTORY dump_dir TO sylvain;


For detailed usage explanations, see


If you can create a database link from your local database to the one that currently contains the data, you can use a package DBMS_DATAPUMP

to copy the entire schema. It is an interface to Datapump (as @Sylvain Leroux mentioned) that can be called from the database.

   dph NUMBER;
   source_schema VARCHAR2 (30) := 'SCHEMA_TO_EXPORT';
   target_schema VARCHAR2 (30) := 'SCHEMA_TO_IMPORT';
   job_name VARCHAR2 (30) := UPPER ('IMPORT_' || target_schema);
   p_parallel NUMBER := 3;
   v_state VARCHAR2 (30);
   dph :=
   DBMS_OUTPUT.put_line ('dph = ' || dph);
   DBMS_DATAPUMP.metadata_filter (dph,
                                  '''' || source_schema || '''');
   DBMS_DATAPUMP.metadata_remap (dph,
   DBMS_DATAPUMP.set_parallel (dph, p_parallel);
   DBMS_DATAPUMP.start_job (dph);
   DBMS_DATAPUMP.wait_for_job (dph, v_state);
   DBMS_OUTPUT.put_line ('Export/Import time: ' || (SYSTIMESTAMP - v_start));
   DBMS_OUTPUT.put_line ('Final state: ' || v_state);


The script actually copies and renames the schema. If you want to keep the same schema name, I believe you just remove the call metadata_remap




SQL Developer can help C # 1 by creating statements INSERT

with formatted query result :

Select /*insert*/ * 
from My_Table;




All Articles