expdp
- RAC环境中,设置cluster=n参数
- 排除统计信息导出: exclude=statistics
- 并行导出: parallel=Number Of CPUs
- RAC环境中,设置PARALLEL_FORCE_LOCAL=TRUE
- 12c中,添加version=12.1以增强兼容性
示例:1
2
3
4expdp system/[email protected] schemas=xxx \
cluster=n exclude=statistics parallel=4 \
PARALLEL_FORCE_LOCAL=true version=12.1 \
directory=xxx dumpfile=xxx.dmp
impdp
- 导入数据后再建立索引、约束等, 以避免产生大量的undo和temp
1 | --生成DDl语句 |
根据sql语句保留约束、索引的创建语句再进行导入:1
2
3
4impdp system/xxxxxxxx directory=datapump dumpfile=xxx.dump \
EXCLUDE=STATISTICS,constraint,index \
remap_tablespace=aaaa:bbbb parallel=4 \
remap_schema=xxx:xxx transform=disable_archive_logging:Y
- 尽量不要使用TABLE_EXISTS_ACTION=APPEND or TABLE_EXISTS_ACTION=TRUNCATE
- 导入时添加transform=disable_archive_logging:Y参数,12c新特性,可以在导入的时候减少redo的产生
Reference:
Error ORA-30036 DataPump Import (IMPDP) Exhausts Undo Tablespace (Doc ID 727894.1)
Import DataPump - How To Limit The Amount Of UNDO Generation of an IMPDP job ? (Doc ID 1670349.1)
EOF