11.09.2009

Different Options for Exports/Imports:

expdp help=y
To estimate size without actually taking the dump:
expdp prod7/thword7 tables=OLD_DATA directory=DUMPS_NAHAR estimate_only=y
=>estimated "PROD7"."OLD_DATA" 156.8 GB

Directory creation
SQL> col DIRECTORY_PATH format a60
SQL> select DIRECTORY_NAME,DIRECTORY_PATH from dba_directories;

SQL> Create or replace directory DUMPS_NAHAR3 As '/oracle';
SQL> grant read,write on directory DUMPS_NAHAR3 to prod7;

EXPORT
expdp single table
expdp prod/pass tables=LOGIN directory=DBDUMPS dumpfile=LOGIN_20100620.dmp logfile=LOGIN_20100620_LOG.log

expdp Multiple tables, starting with different name
expdp prod/
pass tables=LOGIN_CLIENTS,TAC_FORM_SUMMARY directory=DUMPS_TAHSEEN dumpfile=DUMPS_TAHSEEN_20100301.dmp logfile=DUMPS_TAHSEEN_EXPORT_LOG_20100301.log

expdp Multiple tables, starting with same name - TMS
expdp prod/pass DIRECTORY=DUMPS_NAHAR_NEPTUNE SCHEMAS=prod INCLUDE=TABLE:"like'TR_%'" DUMPFILE=DUMPS_ZAHIDUL_20100308.dmp logfile=DUMPS_ZAHIDUL_EXPORT_LOG_20100308.log

Example - Exclude tables
expdp prod/
pass DIRECTORY=DUMPS_TERMINUS_PROD7_20100708 SCHEMAS=PROD EXCLUDE=TABLE:\"IN \(\'OLD_DATA\',\'USER_ACTIVITY\'\)\" DUMPFILE=DUMPS_TERMINUS_PROD_20100708.dmp logfile=DUMPS_TERMINUS_PROD_20100708_LOG_20100308.log

exclude=TABLE:\"='TABLE_NAME'\"
EXCLUDE=TABLE:\"IN \(\'TEMP\',\'TEMP1\'\)\"

Full schema
expdp atiq2/pass schemas=atiq2 directory=DUMPS_NAHAR3 dumpfile=atiq2_nd_20100907.dmp logfile=exp_atiq2_nd_20100907.log

In multiple file
expdp prod7/pass SCHEMAS=prod7 DIRECTORY=data_pump_dir DUMPFILE=saturn_prod7_%U.dmp FILESIZE=5G LOGFILE=saturn_prod7_exp.log


IMPORT
1: TABLE_EXISTS_ACTION=replace
impdp prod/pass DIRECTORY=DUMPS_NAHAR_NEPTUNE SCHEMAS=prod DUMPFILE=dbserver_scema_date.dmp TABLE_EXISTS_ACTION=replace PARALLEL=3

Full schema
impdp prod/pass DIRECTORY=DUMPS_NAHAR_NEPTUNE DUMPFILE=df01_prod_flexdoc_smaruf_nahar_20101012.dmp TABLE_EXISTS_ACTION=replace PARALLEL=3

2: REMAP_SCHEMA
impdp system/pass DIRECTORY=DUMPS_NAHAR4 DUMPFILE=atiq2_nd_20100907.dmp REMAP_SCHEMA=atiq2:states PARALLEL=3

-- REMAP_TABLE: We can't do it in 10g. This is only available in 11g.

3: CONTENT=DATA_ONLY/
METADATA_ONLY
impdp system/pass DIRECTORY=DUMPS_NAHAR CONTENT=DATA_ONLY DUMPFILE=OLD_DATA_20100705.dmp REMAP_SCHEMA=PROD7:NAHAR PARALLEL=3

4: Import on a fresh a schema
impdp states/pass DIRECTORY=DUMPS_NAHAR_NEPTUNE SCHEMAS=states DUMPFILE=states_dump_20100919.dmp PARALLEL=3

--wrong
expdp prod/pass DIRECTORY=DUMPS_TERMINUS_PROD_20100708 SCHEMAS=PROD DUMPFILE=DUMPS_TERMINUS_PROD_20100708.dmp logfile=DUMPS_TERMINUS_PROD_20100708_LOG_20100308.log

--If I wanted to take dump from different directory then I should use like:
DUMPFILE=datadir1:schema1%U.dmp,datadir2:schema2%U.dmp

How to zip & unzip
zip: nahar@mf01:~$ gzip df01_prod7_flexdoc_smaruf_nahar_201001012.dmp
zip: nahar@mf01:~$ gunzip df01_prod7_flexdoc_smaruf_nahar_20101012.zip

More:
www.oracle-dba-online.com
http://www.oracle-base.com/articles/10g/OracleDataPump10g.php