Expdp/impdp常用方法总结

导出数据测试

expdp \'/ as sysdba \' schemas=HAIERTMS,HAIERTMS2 DIRECTORY=EXPDP_DIR EXCLUDE=TABLE:\"IN \(\'TMS_RECORD_INTERFACELOG\',\'THORN_INTERFACE_LOG\',\'TMS_ORDER_LOGISTICS_NODES\',\'THORN_RULE_EXCEPTION_LOG\',\'TMS_ORDER_BAK\',\'HAIERTMS_ORDER_P_TEST\',\'EXCEPTION_LOG\',\'TMS_ORDER_DETAIL_P\',\'TMS_BILL_TASK_BAK\',\'TMS_BILL_TASK_P\',\'TMS_LEG_DETAIL_P\'\)\" ESTIMATE_ONLY=y

 

按schema导入导出

expdp \'/ as sysdba \' schemas=APEX_COMMON DIRECTORY=DATA_PUMP_DIR CONTENT=METADATA_ONLY DUMPFILE=APEX_COMMON0811.dmp logfile=APEX_COMMON0811.log
impdp \'/ as sysdba \' DIRECTORY=DATA_PUMP_DIR CONTENT=METADATA_ONLY DUMPFILE=APEX_COMMON0811.dmp logfile=IMP_APEX_COMMON0811.log remap_schema=APEX_COMMON:APEX_TW_COMMON

 

带where条件的导出

expdp \'/ as sysdba \' directory=EXPDP_DIR_NFS tables=tbs.TBS_TELEPHONE_BILL query="'where YEAR = 2016 and MONTH = 11'" dumpfile=TBS_TELEPHONE_BILL170111.dmp logfile=TBS_TELEPHONE_BILL170111.log PARALLEL=2
expdp \'/ as sysdba \' directory=EXPDP_DIR_NFS tables=tbs.TBS_BILL_CALL_RECORD query="'where YEAR = 2016 and MONTH = 11'" dumpfile=TBS_BILL_CALL_RECORD170111.dmp logfile=TBS_BILL_CALL_RECORD170111.log PARALLEL=2
expdp \'/ as sysdba \' directory=EXPDP_DIR_NFS tables=tbs.TBS_TELEPHONE_BILL_SUM query="'where YEAR = 2016 and MONTH = 11'" dumpfile=TBS_TELEPHONE_BILL_SUM170111.dmp logfile=TBS_TELEPHONE_BILL_SUM170111.log PARALLEL=2

 

带parfile的导出

expdp \'/ as sysdba \' schemas=APONV2 DIRECTORY=EXPDP_DIR_NAS dumpfile=APONV2_tables170204.dmp logfile=APONV2_tables170204.log PARFILE=exclude_table.txt

 

只导表结构

expdp hdoa/xxxxx DIRECTORY=expbk CONTENT=METADATA_ONLY DUMPFILE=hd20160114.dmp logfile=arp20160114.log

 

排除表

expdp system/xxxxx schemas=HAIERTMS dumpfile=HAIERTMS0414.dmp version=11.2.0.3 DIRECTORY=DUMP logfile=HAIERTMS0414.log EXCLUDE=TABLE:\"IN \(\'THORN_INTERFACE_LOG\',\'THORN_RULE_EXCEPTION_LOG\'\)\"

 

指定表

expdp system/xxxxx content=metadata_only version=11.2.0.3 DIRECTORY=DUMP dumpfile=HAIERTMS0414_structure.dmp logfile=TMS0414_structure.log tables=TMS.THORN_INTERFACE_LOG,TMS._RULE_EXCEPTION_LOG

 

转载于:https://www.cnblogs.com/godspeed034/p/7356945.html