ORA-01555: snapshot too old: rollback segment number 16 with name "_SYSSMU16_62169813$" too small

最新推荐文章于 2025-07-18 11:05:02 发布

原创最新推荐文章于 2025-07-18 11:05:02 发布 · 8.4k 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

Study & Testing

本文详细记录了在使用Oracle数据库导出工具expdp时遇到ORA-01555错误的原因及解决方案。该错误提示为“snapshot too old”，即快照过旧，导致无法正确读取数据。通过分析错误信息和执行相关步骤，成功解决了数据过旧导致的导出失败问题。

DM00 started with pid=33, OS id=12315, job SYS.SYS_EXPORT_SCHEMA_01

Sat Nov 01 16:37:16 2014
DW00 started with pid=37, OS id=12319, wid=1, job SYS.SYS_EXPORT_SCHEMA_01
ORA-01555 caused by SQL statement below (SQL ID: 4df4k37n81crb, SCN: 0x0000.0014e94e):
SELECT * FROM RELATIONAL("SCOTT"."TEST5")
Sat Nov 01 16:38:58 2014
DM00 started with pid=33, OS id=12366, job SYS.SYS_EXPORT_SCHEMA_01
Sat Nov 01 16:39:00 2014
DW00 started with pid=37, OS id=12370, wid=1, job SYS.SYS_EXPORT_SCHEMA_01
ORA-01555 caused by SQL statement below (SQL ID: 4df4k37n81crb, SCN: 0x0000.0014e94e):
SELECT * FROM RELATIONAL("SCOTT"."TEST5")

[oracle@oratest trace]$ sqlplus / as sysdba

[oracle@oratest expdpdir]$ expdp \"/ as sysdba\" directory=dpdata dumpfile=mydb_%U.dmp schemas=scott exclude=TABLE:\"IN$\'EMP\',\'DEPT\'$\" FLASHBACK_SCN=1370446

Export: Release 11.2.0.4.0 - Production on Sat Nov 1 16:51:58 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=dpdata dumpfile=mydb_%U.dmp schemas=scott exclude=TABLE:"IN('EMP','DEPT')" FLASHBACK_SCN=1370446
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 24.25 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DINYA_TEST":"PART_01" 6.015 KB 0 rows
. . exported "SCOTT"."DINYA_TEST":"PART_02" 6.070 KB 2 rows
. . exported "SCOTT"."DINYA_TEST":"PART_03" 6.117 KB 4 rows
. . exported "SCOTT"."BONUS" 5.929 KB 0 rows
. . exported "SCOTT"."SALGRADE" 5.679 KB 5 rows
. . exported "SCOTT"."TEST3" 4.929 KB 0 rows
ORA-31693: Table data object "SCOTT"."TEST5" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01466: unable to read data - table definition has changed
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/expdpdir/mydb_01.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" completed with 1 error(s) at Sat Nov 1 16:52:07 2014 elapsed 0 00:00:07

[oracle@oratest expdpdir]$ rm -rf *
[oracle@oratest expdpdir]$ expdp \"/ as sysdba\" directory=dpdata dumpfile=mydb_%U.dmp schemas=scott exclude=TABLE:\"IN$\'EMP\',\'DEPT\'$\" FLASHBACK_SCN=1370446

Export: Release 11.2.0.4.0 - Production on Sat Nov 1 16:52:33 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=dpdata dumpfile=mydb_%U.dmp schemas=scott exclude=TABLE:"IN('EMP','DEPT')" FLASHBACK_SCN=1370446
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 24.18 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DINYA_TEST":"PART_01" 6.015 KB 0 rows
. . exported "SCOTT"."DINYA_TEST":"PART_02" 6.070 KB 2 rows
. . exported "SCOTT"."DINYA_TEST":"PART_03" 6.117 KB 4 rows
. . exported "SCOTT"."BONUS" 5.929 KB 0 rows
. . exported "SCOTT"."SALGRADE" 5.679 KB 5 rows
. . exported "SCOTT"."TEST3" 4.929 KB 0 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/expdpdir/mydb_01.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Nov 1 16:52:42 2014 elapsed 0 00:00:07