Pre-Requisites

Export

Single Schema Export

export SCHEMA=schema1

export OUTDIR=ORAOUT

expdp SCHEMAS=${SCHEMA} DIRECTORY=${OUTDIR} DUMPFILE=${SCHEMA}.dmp LOGFILE=${SCHEMA}.explog

...where SCHEMA1 is an example Schema name and ORAOUT is an example Directory name.

Single Schema Export (no data)

To export a schema definition only (without any data):

export SCHEMA=schema1

export OUTDIR=ORAOUT

expdp SCHEMAS=${SCHEMA} DIRECTORY=${OUTDIR} DUMPFILE=${SCHEMA}.dmp LOGFILE=${SCHEMA}.explog CONTENT=metadata_only

Multiple Schema Export

export SCHEMA=schema1,schema2,schema3,etc

export DMPNAME=NameOfDumpfileWithoutDmpSuffix

export OUTDIR=ORAOUT

expdp SCHEMAS=${SCHEMA} DIRECTORY=${OUTDIR} DUMPFILE=${DMPNAME}.dmp LOGFILE=${DMPNAME}.explog

Single Table Export

export SCHEMA=schema1

export OUTDIR=ORAOUT

export TABLE=table

expdp TABLES=${SCHEMA}.${TABLE} DIRECTORY=${OUTDIR} DUMPFILE=${TABLE}.dmp LOGFILE=${TABLE}.explog

Multiple Table Export (PARFILE)

cat > /tmp/parfile.txt <<EOF

SCHEMAS=schema1

DIRECTORY=ORAOUT

DUMPFILE=dumpfile.dmp

INCLUDE=TABLE:"IN ('table1','table2','table3','etc')"

LOGFILE=dumpfile.explog

EOF

expdp PARFILE=/tmp/parfile.txt

Compression

If you have a license for the Advanced Compression option you can add this option to your exports...

COMPRESSION=ALL

Consistent Export

CONSISTENT=Y

which is synonymous with this (without the work of finding out the scn)...

FLASHBACK_SCN=SCN

Alternatively use soemthing like..

FLASHBACK_TIME=SYSTIMESTAMP

Package Export

The easiest way to do this is to use SQL Developer. Right click on the Package and select "export". This will start a Wizard to guide you through the steps to create an SQL file that can be run to recreate the package.

User Export

If you want to copy a user from one instance to another without taking any objects then the easiest way to to this is by using DBMS_METADATA. See the "Users" page for an example of doing this.

Import

Single Schema Import

export SCHEMA=schema1

export OUTDIR=ORAOUT

impdp SCHEMAS=${SCHEMA} DIRECTORY=${OUTDIR} DUMPFILE=${SCHEMA}.dmp LOGFILE=${SCHEMA}.implog

Multiple Schema Import

export SCHEMA=schema1,schema2,schema3,etc

export DMPNAME=NameOfDumpfileWithoutDmpSuffix

export OUTDIR=ORAOUT

impdp SCHEMAS=${SCHEMA} DIRECTORY=${OUTDIR} DUMPFILE=${DMPNAME}.dmp LOGFILE=${DMPNAME}.implog

without Grants and Synonyms...

impdp SCHEMAS=${SCHEMA} DIRECTORY=${OUTDIR} DUMPFILE=${DMPNAME}.dmp LOGFILE=${DMPNAME}.implog EXCLUDE=GRANTS,SYNONYMS

Table Import

export SCHEMA=schema1

export TABLE=table1

export OUTDIR=ORAOUT

impdp TABLES=${SCHEMA}.${TABLE} DIRECTORY=${OUTDIR} DUMPFILE=${TABLE}.dmp LOGFILE=${TABLE}.implog

Multiple Table Import (PARFILE)

cat > /tmp/parfile.txt <<EOF

SCHEMAS=schema1

DIRECTORY=ORAOUT

DUMPFILE=dumpfile.dmp

INCLUDE=TABLE:"IN ('table1','table2','table3','etc')"

LOGFILE=dumpfile.implog

REMAP_TABLESPACE=source_ts:target_ts

EOF

impdp PARFILE=/tmp/parfile.txt

Schema Duplicate

To duplicate a schema from an existing Schema Export file...

export SOURCE_SCHEMA=schema1

export TARGET_SCHEMA=schema2

export SOURCE_TS=tablespace1

export TARGET_TS=tablespace2

export OUTDIR=ORAOUT

impdp SCHEMAS=${SOURCE_SCHEMA} DIRECTORY=${OUTDIR} DUMPFILE=${SOURCE_SCHEMA}.dmp LOGFILE=${SOURCE_SCHEMA}.duplog \

      REMAP_SCHEMA=${SOURCE_SCHEMA}:${TARGET_SCHEMA} REMAP_TABLESPACE=${SOURCE_TS}:${TARGET_TS}

Generate DDL

export SCHEMA=schema1,schema2,schema3,etc

export DMPNAME=NameOfDumpfileWithoutDmpSuffix

export OUTDIR=ORAOUT

impdp SCHEMAS=${SCHEMA} DIRECTORY=${OUTDIR} DUMPFILE=${DMPNAME}.dmp LOGFILE=${DMPNAME}.ddllog SQLFILE=${DMPNAME}.sql

DataPump API

Schema Duplicate

The example below gives the minimum recommended commands to clone an existing schema to a new schema.

DECLARE

  jobhandle NUMBER;

BEGIN

   jobhandle := DBMS_DATAPUMP.OPEN(operation   => 'IMPORT',

                                   job_mode    => 'SCHEMA',

                                   remote_link => 'DBLINKNAME',

                                   job_name    => 'SOURCE_SCHEMA_CLONE');

   DBMS_DATAPUMP.ADD_FILE (handle    => jobhandle,

                           filename  => 'SOURCE_SCHEMA_CLONE.log',

                           directory => 'ORAOUT',

                           filetype  => DBMS_DATAPUMP.ku$_file_type_log_file);

   DBMS_DATAPUMP.METADATA_FILTER(jobhandle,'SCHEMA_LIST','''SOURCE_SCHEMA''');

   DBMS_DATAPUMP.METADATA_REMAP(jobhandle,'REMAP_SCHEMA','SOURCE_SCHEMA','TARGET_SCHEMA');

   DBMS_DATAPUMP.METADATA_REMAP(jobhandle,'REMAP_TABLESPACE','SOURCE_TS','TARGET_TS');

   DBMS_DATAPUMP.START_JOB(jobhandle);

END;

/

Some useful optional parameters (to be placed before DBMS_DATAPUMP.START_JOB in the example above)...

   DBMS_DATAPUMP.METADATA_FILTER((jobhandle,'EXCLUDE_PATH_EXPR',''GRANTS'');

   DBMS_DATAPUMP.SET_PARAMETER(jobhandle,'FLASHBACK_SCN',999999999999);

   DBMS_DATAPUMP.SET_PARAMETER(jobhandle,'TABLE_EXISTS_ACTION','REPLACE');

Schema Duplication Notes

Characterset

Run on both Source and Target databases...

SELECT value 

FROM NLS_DATABASE_PARAMETERS 

WHERE parameter='NLS_CHARACTERSET';

If the target database has a characterset that is different to the source database then there could be data loss (unless the target database characterset is a subset of the source database). For example, if you export from a database with WE8MSWIN1252 and import into a database with WE8ISO8859P1 then any occurrences of the Euro symbol will be lost. You are likey to get errors in the DataPump import log (e.g. ORA-39346)

Synonyms

Run on the Source database...

SET LINESIZE 132

SET PAGESIZE 40

COLUMN SYNONYM_NAME FORMAT A30

COLUMN TABLE_OWNER  FORMAT A30

COLUMN TABLE_NAME   FORMAT A30

SELECT synonym_name, table_owner, table_name

FROM dba_synonyms

WHERE owner = '&&SCHEMA'

AND table_owner != '&SCHEMA';

If the table_owners and table_names do not exist in the target database then you will get errors in the DataPump import log (e.g. ORA-39082)

Troubleshooting

Poor Performance

If data pump export starts to work very slow then check "EXPDP And IMPDP Slow Performance In 11gR2 and 12cR1 And Waits On Streams AQ: Enqueue Blocked On Low Memory (Doc ID 1596645.1)"

ALTER SYSTEM SET STREAMS_POOL_SIZE=200M SCOPE=BOTH;

If increasing STREAMS_POOL_SIZE does not help then you have to restart database also.

ORA-02304: invalid object identifier literal

ORA-39083: Object type TYPE failed to create with error:

ORA-02304: invalid object identifier literal

CAUSE

During Schema Duplication... The object types already exist in the source schema. When the types are exported, we also export the object_identifier (OID) of the types. Within the current archictecture, the object-identifier needs to be unique in the database. During import (into the same database), we try to create the same object types in the target schema. As the OID of the types already exists in the source schema, the types cannot be created in the target schema.

SOLUTION

Use the Import DataPump parameter TRANSFORM which enables you to alter object creation DDL for the types. The value N  for the transform name OID implies that a new OID is assigned. 

export SOURCE_SCHEMA=schema1

export TARGET_SCHEMA=schema2

export SOURCE_TS=tablespace1

export TARGET_TS=tablespace2

export OUTDIR=ORAOUT

impdp SCHEMAS=${SOURCE_SCHEMA} DIRECTORY=${OUTDIR} DUMPFILE=${SOURCE_SCHEMA}.dmp LOGFILE=${SOURCE_SCHEMA}.duplog \

      REMAP_SCHEMA=${SOURCE_SCHEMA}:${TARGET_SCHEMA} TRANSFORM=oid:n

ORA-39082: Object type ... created with compilation warnings

ORA-39082: Object type OBJECT TYPE:"OWNER"."OBJECT_NAME" created with compilation warnings

CAUSE

It is likely that a dependency does not exist in the target database (i.e. this error is more common when performing a schema duplicate between two different Oracle Instances).

SOLUTION

Use SQL Developer to attempt to compile the object on the target instance and review the errors

Bibliography