Datapump (export and import database objects)

From Robs_Wiki
Revision as of 10:03, 24 February 2020 by Qadmin wiki (talk | contribs) (Export and Import a Schema)
Jump to: navigation, search

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