oracle+大表+拆分,expdp ORA-01555(二)(大表拆分)

expdp ORA-01555(二)(大表拆分)

环境信息：

DB:Oracle 11.2.0.1.0

OS:Windows Server 2012

---敏感数据已替换

问题：

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 2019

Archived Log entry 6560 added for thread 1 sequence 46359 ID 0x7262bbad dest 1:

XXX XXX XXX 2019

ORA-01555 caused by SQL statement below (SQL ID: 88tquba1dj6s0, SCN: 0x0000.47dd74a2):

SELECT * FROM RELATIONAL("CHENJCH"."T_XXX_XXXXX")

XXX XXX XXX 2019

问题分析：

大表数据量增长很快，增加retention后只能缓解几天，无限制加大retention也不现实；

http://blog.itpub.net/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')='2019'\" exclude=STATISTICS

expdp chenjch/***** directory=dir_chenjch dumpfile=%backupfile4% logfile=%logfile4% tables=T_XXX_XXXXX

query=T_XXX_XXXXX:\"where to_char(XXXTime,'yyyy')='2018'\" exclude=STATISTICS

--大表拆分后可以正常导出了

欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！