Difference between revisions of "Datapump (export and import database objects)"

From Robs_Wiki
Jump to: navigation, search
(Export Database Links of multiple Schemas)
(Export and Import a Schema)
Line 39: Line 39:
 
$ impdp scott/tiger@prod remap_schema=SCOTT:ROBX directory=MY_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log
 
$ impdp scott/tiger@prod remap_schema=SCOTT:ROBX directory=MY_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log
  
 +
</pre>
 +
== Export a Schema, but only the tables ==
 +
<pre>
 +
full=y
 +
INCLUDE=TABLE, SCHEMA:"IN('INFORMATICA')"
 +
directory=ROB_OUTBOUND
 +
dumpfile=exp_OUTBOUND.dmp
 +
logfile=exp_OUTBOUND.log
 
</pre>
 
</pre>
  

Revision as of 10:03, 24 February 2020

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')"
directory=ROB_OUTBOUND
dumpfile=exp_OUTBOUND.dmp 
logfile=exp_OUTBOUND.log

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