Datapump (export and import database objects)
Contents
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