expdp help=yTo 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