Oracle DB - datapump expdp/impdp 指令參考

rm HR1_EMP_TBLS.dmp ;expdp system DIRECTORY=DATA_PUMP_DIR TABLES=HR1.EMPLOYEES,HR1.EMPLOYEES_TEST DUMPFILE=HR1_EMP_TBLS.dmp


$ cat hr1.par
TABLES=HR1.EMPLOYEES,HR1.EMPLOYEES_TEST
REMAP_TABLE=HR1.EMPLOYEES:SPC_EMPLOYEES,HR1.EMPLOYEES_TEST:SPC_EMPLOYEES_TEST
REMAP_SCHEMA=HR1:HR

impdp system DIRECTORY=DATA_PUMP_DIR parfile=hr1.par exclude=index,statistics DUMPFILE=HR1_EMP_TBLS.dmp

impdp system DIRECTORY=DATA_PUMP_DIR parfile=hr1.par exclude=index,statistics  TABLE_EXISTS_ACTION=TRUNCATE DUMPFILE=HR1_EMP_TBLS.dmp


以下引用自 
IT 研究室 ( 前IT DBA's 資訊站)

Oracle data pump 指令參照

預測dmp檔佔多少空間
expdp test/test directory=expdp_dir logfile=test_dmp.log estimate_only=y
                ESTIMATE=BLOCKS  (預設)
                ESTINATE=STATISTICS  (依照表格的STATISTICS預測空間大小)
指定dump時用幾個執行緒
expdp test/test  directory=expdp_dir dumpfile=test.dmp logfile=test_dmp.log parallel=4

排除某個表格
expdp test/test schemas=TEST exclude=TABLE:"in\('TEST'\)" directory=expdp_dir dumpfile=expdp.dmp logfile=expdp.log

排除某個USER
expdp \'/ as sysdba\' full=y EXCLUDE=SCHEMA:"in\('TEST'\)" directory=dp dumpfile=full.dmp logfile=full.log

排除一
expdp \'/ as sysdba\' full=y EXCLUDE=SCHEMA:"in\('WKSYS'\)" directory=dp dumpfile=full.dmp logfile=full.log CONTENT=METADATA_ONLY

expdp \'/ as sysdba\' full=y EXCLUDE=SCHEMA:"in\('WKSYS','OLAPSYS','TSMSYS','SYSMAN','OUTLN','WK_TEST'\)" directory=dp dumpfile=full.dmp logfile=full.log


匯出所有METADATA
expdp \'/ as sysdba\' SCHEMAS=TEST,JAYCHU directory=dp dumpfile=full.dmp logfile=full.log CONTENT=METADATA_ONLY
by default, the datapump points to $ORACLE_BASE_admin/SID/dpdump

--export table
drop directory expdp_dir ;
create directory expdp_dir as '/na02/sysage/0825';
expdp \'/ as sysdba\' tables=MISUSER.ST02PF directory=expdp_dir content=all dumpfile=ST02PF.dmp logfile=ST02PF.log

create directory expdp_dir as '/na02/sysage/0825';
impdp \'/ as sysdba\' tables=MISUSER.ST02PF directory=expdp_dir content=all dumpfile=ST02PF.dmp logfile=ST02PF_imp.log TABLE_EXISTS_ACTION=replace
--

impdp \'/ as sysdba\'  DIRECTORY=dp REMAP_SCHEMA=JAYCHU:JAYCHU123  SQLFILE=JAYCHU123.sql LOGFILE=impdp.log DUMPFILE=full.dmp INCLUDE=GRANT,TABLE
                       ESTIMATE=STATISTICS
test:
impdp \'/ as sysdba\'  DIRECTORY=dp REMAP_SCHEMA=JAYCHU:JAYCHU123 LOGFILE=impdp.log DUMPFILE=full.dmp INCLUDE=GRANT,TABLE  ESTIMATE=STATISTICS


(PS: CONTENT=DATA_ONLY is invalid for SQL_FILE jobs.)
   
impdp \'/ as sysdba\'  DIRECTORY=dp REMAP_SCHEMA=JAYCHU:JAYCHU123  PARALLEL=4 LOGFILE=impdp.log DUMPFILE=full.dmp
                       CONTENT= ALL, (METADATA_ONLY), DATA_ONLY
                       INCLUDE=TABLE_DATA or TABLE or PROCEDURE or FUNCTION or TRIGGER or GRANT or INDEXES or CONSTRAINT
                       EXCLUDE=TABLE_DATA or TABLE or PROCEDURE or FUNCTION or TRIGGER or GRANT or INDEXES or CONSTRAINT(可自行斟酌須排除schema object type)
                       remap_tablespace=old_tablespace:new_tablespace
                       TABLE_EXISTS_ACTION   匯入之物件已經存在時所要採取的動作. : (SKIP , TRUNCATE , REPLACE)
                                                                                    ^^^^ default

create directory expdp_dir as '/na02/sysage/0825';
expdp \'/ as sysdba\' full=y  PARALLEL=4 flashback_time=\"TO_TIMESTAMP\(\'2010-07-19 20:40:00\', \'YYYY-MM-DD HH24:MI:SS\'\)\"  dumpfile=full0719.dmp

create directory expdp_dir as '/na02/sysage/0719';
impdp \'/ as sysdba\' DIRECTORY=expdp_dir PARALLEL=4 dumpfile=full0719.dmp logfile=venus_impdp_1.log CONTENT=DATAONLY TABLE_EXISTS_ACTION=REPLACE INCLUDE=INDEXES&



查看job
col OWNER_NAME for a10
col JOB_NAME for a20
col OPERATION for a6
col JOB_MODE for a6
col STATE for a10
col DEGREE for 9.9
col ATTACHED_SESSIONS for 9
col DATAPUMP_SESSIONS for 9

select * from dba_datapump_jobs;

查看expdp進度
col opname for a30
select opname , sofar , totalwork , ROUND(SOFAR/TOTALWORK*100,2) "%_Complete" from v$session_longops where sofar !=totalwork and opname like '%EXPORT%';

impdp進度
col opname for a30
select opname , sofar , totalwork , ROUND(SOFAR/TOTALWORK*100,2) "%_Complete" from v$session_longops where sofar !=totalwork and opname like '%IMPORT%';