Datapump (export and import database objects)

From Robs_Wiki
Revision as of 11:02, 11 February 2020 by Qadmin wiki (talk | contribs) (Export Database Links of multiple Schemas)
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 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)"

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