查询oracle报01555的SQL,案例:Oracle报错ORA-01555 Query Duration=0 sec报错解决思路

天萃荷净

运维DBA在巡检时发现alert日志文件中存在错误，奇怪之处:Query Duration=0 sec,竟然出现了ORA-01555

1.ALERT日志错误

奇怪之处:Query Duration=0 sec,竟然出现了ORA-01555

Tue Feb 7 02:41:34 2012

ORA-01555 caused by SQL statement below (Query Duration=0 sec, SCN: 0x0b2e.efcd78a9):

Tue Feb 7 02:41:34 2012

SELECT "ID_NO","CUST_ID" FROM "DBACCADM"."DCUSTMSG" "C" WHERE "ID_NO"=:1

2.ORA-01555解释

超过了undo_retention时间，undo被覆盖导致ORA-01555

[zwq_acc1:/home/oraeye/check]oerr ora 1555

01555, 00000, "snapshot too old: rollback segment number %s with name \"%s\" too small"

// *Cause: rollback records needed by a reader for consistent read are

// overwritten by other writers

// *Action: If in Automatic Undo Management mode, increase undo_retention

// setting. Otherwise, use larger rollback segments

3.查询数据库版本

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production

PL/SQL Release 9.2.0.8.0 - Production

CORE 9.2.0.8.0 Production

TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production

NLSRTL Version 9.2.0.8.0 - Production

4.查询undo基本使用信息

从这里可以发现，两个节点的undo表空间还有很多剩余，缺发生了undo被覆盖从而出现了ORA-01555

SQL> col name for a20

SQL> col value for a15

SQL> SELECT INST_ID, NAME, VALUE

2 FROM GV$PARAMETER

3 WHERE UPPER (Name) LIKE '%UNDO%';

INST_ID NAME VALUE

---------- -------------------- ---------------

1 undo_management AUTO

1 undo_tablespace UNDOTBS1

1 undo_suppress_errors FALSE

1 undo_retention 1800

2 undo_management AUTO

2 undo_tablespace UNDOTBS2

2 undo_suppress_errors FALSE

2 undo_retention 1800

8 rows selected.

TABLESPACE_NAME CURRENT_TOTAL(MB) USED(MB) FREE(MB) FREE% AUT MAX_TOTAL(MB)

------------------------------ ----------------- ---------- ---------- ---------- --- -------------

UNDOTBS1 40950 1587.94 39362.0625 96.12 NO 40950

UNDOTBS2 57330 1926.31 55403.6875 96.64 NO 57330

SQL> SELECT DISTINCT STATUS ,

2 COUNT(*) "EXTENT_NUM",

3 SUM(BYTES) / 1024 / 1024 / 1024 "UNDO(G)"

4 FROM DBA_UNDO_EXTENTS

5 GROUP BY STATUS;

STATUS EXTENT_NUM UNDO(G)

--------- ---------- ----------

ACTIVE 208 .273658752

EXPIRED 7651 2.42865753

UNEXPIRED 941 .752548218

查询MOS[ID 761128.1]，发现可能是Oracle bug导致(BUG:6799685 – ORA-1555 ERROR WITH QUERY DURATION=0 AND UNDO_RETENTION=1800和BUG:5475085 – V$UNDOSTAT.EXPBLKREUCNT IS NEVER INCREMENTED)

5.ORA-01555解决方法

Increase the size of the UNDO tablespace and increase the UNDO_RETENTION parameter value to try to prevent required undo expiring too quickly.

基于本库，因为undo空间还有很大剩余，直接设置UNDO_RETENTION=3600即可(可以从一定程度上缓解整个问题，但是要从根本上解决整个问题，需要升级到10.2.0.4及其以上版本)

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之案例:Oracle报错ORA-01555 Query Duration=0 sec报错解决思路