expdp ORA-01555(二)(大表拆分)
环境信息:
DB:Oracle 11.2.0.1.0
OS:Windows Server
---敏感数据已替换
问题:
expdp导出一张含有BLOG字段的大表(100G)时,报错ORA-01555
---expdp_log
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-31693: Table data object "CHENJCH"."T_XXX_XXXXX" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10_XXXXXXXXX$" too small
---alert_XXX.log
XXX XXX XXX
Archived Log entry 6560 added for thread 1 sequence 46359 ID 0x7262bbad dest 1:
XXX XXX XXX
ORA-01555 caused by SQL statement below (SQL ID: 88tquba1dj6s0, SCN: 0x0000.47dd74a2):
SELECT * FROM RELATIONAL("CHENJCH"."T_XXX_XXXXX")
XXX XXX XXX
问题分析:
大表数据量增长很快,增加retention后只能缓解几天,无限制加大retention也不现实;
/29785807/viewspace-2218168/
单个大表可以根据时间进行数据拆分成逻辑上的多个小表,在进行导出即可;
解决方案:
更改备份脚本
原脚本
expdp chenjch/***** directory=dir_chenjch dumpfile=%backupfile1% logfile=%logfile1% schemas=chenjch exclude=STATISTICS
新脚本
---1 先将大表排查
expdp chenjch/***** directory=dir_chnenjch dumpfile=%backupfile1% logfile=%logfile1% schemas=chenjch
exclude=TABLE:\"IN\(\'T_XXX_XXXXX\'\)\" exclude=STATISTICS
---2 将大表按年拆分
expdp chenjch/***** directory=dir_chenjch dumpfile=%backupfile3% logfile=%logfile3% tables=T_XXX_XXXXX
query=T_XXX_XXXXX:\"where to_char(XXXTime,'yyyy')=''\" exclude=STATISTICS
expdp chenjch/***** directory=dir_chenjch dumpfile=%backupfile4% logfile=%logfile4% tables=T_XXX_XXXXX
query=T_XXX_XXXXX:\"where to_char(XXXTime,'yyyy')=''\" exclude=STATISTICS
--大表拆分后可以正常导出了
欢迎关注我的微信公众号"IT小Chen",共同学习,共同成长!!!
如果觉得《oracle+大表+拆分 expdp ORA-01555(二)(大表拆分)》对你有帮助,请点赞、收藏,并留下你的观点哦!