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%';
