Datapump (export and import database objects)

Export Individual Tables

Create a directory:

SQL> create directory RMAN_OTHER as '/rman/OTHER';

Create the following parameter file:

DIRECTORY=RMAN_OTHERS
DUMPFILE=my_tables.dmp
LOGFILE=my_tables.log
TABLES=PROD.TABLE1, PROD.TABLE2

Start the export:

$ expdp \"/ as sysdba\" parfile=my_tables.par

Import Individual Tables

$ impdp \"/ as sysdba\" parfile=imp.par

DIRECTORY=ROB_DIR
DUMPFILE=my_table.dmp
LOGFILE=imp_my_table.log

Export and Import a Schema

-- from the command line
 
$ expdp scott/tiger@prod schemas=SCOTT directory=MY_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log
 
$ impdp scott/tiger@prod schemas=SCOTT directory=MY_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log

-- import to other schema

$ impdp scott/tiger@prod remap_schema=SCOTT:ROBX directory=MY_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log

Export a Schema, but only the tables

full=y
INCLUDE=TABLE, SCHEMA:"IN('INFORMATICA', 'SCHEMA2')"
directory=ROB_OUTBOUND
dumpfile=exp_OUTBOUND.dmp 
logfile=exp_OUTBOUND.log

Perform an export and import in parallel

When you want to export and import in parallel you need to add the following parameters to both the export and import file:

parallel=8
dumpfile=export_%U.dmp

Export Database Links of multiple Schemas

$ expdb / as sysdba parfile=dblink.par

Contents parfile:

DIRECTORY=ROB_DBLINKS
DUMPFILE=DBLINKS.dmp
LOGFILE=DBLINKS.log
SCHEMAS=SCOTT,HR,ROBLA,PROD-USER
INCLUDE=DB_LINK

Other example of a parfile, with a select query:

DIRECTORY=ROB_DIR
DUMPFILE=DB_LINKS_TSTDB
LOGFILE=DB_LIKNKS_TSTDB.log
full=y
INCLUDE=DB_LINK:"IN(SELECT db_link FROM dba_db_links)"

remark: if there are database links belonging to SYS, they will not be exported this way, refer to Oracle Note Ora-39165: Schema Sys Was Not Found. [ID 553402.1]: "There is a restriction on Expdp. It cannot export system schemas like SYS, ORDSYS, EXFSYS, MDSYS, DMSYS, CTXSYS, ORDPLUGINS, LBACSYS, XDB, SI_INFORMTN_SCHEMA, DIP, DBSNMP and WMSYS in any mode."

Generate SQL File from export file

You can use the Datapump Import utility to generate a SQL file from the dumpfile. This is done with the SQLFILE parameter and when you specify this parameter, the data is not imported, but only a SQL file is generated. See the sample parameter file below:

DIRECTORY=ROB_DBLINKS
DUMPFILE=DBLINKS.dmp
LOGFILE=DBLINKS.log
SQLFILE=DBLINKS.sql

Another example:

impdp \"/ as sysdba\" directory=ROB_DIR dumpfile=DB_LINK_TSTDB.dmp logfile=IMP_DB_LINKS_TSTDB.log sqlfile=cr_dblinks.sql full=y