ORA-01555是一个很经典很常见的错误.产生的原因是你的查询sql在构造CR的时候,发现undo中的CR块被覆盖了,就会产生ORA-01555.
一般的解决思路:
1.优化sql
2.加大undo表空间
3.加大undo_retention,加大undo的保留时间
但是我这次遇到的确不是上面的情况.
首先现象是,一个简单的查询,报错ORA-01555,快照过旧:
Select * From 住院费用记录 t Where t.病人id=1023738;
这个sql语句非常简单,病人ID上面也有索引,但是一执行就会报错ORA-01555.查询alert日志,如下:
Fri Oct 11 11:34:28 2019
ORA-01555 caused by SQL statement below (SQL ID: gmwgdkyndx34j, Query Duration=0 sec, SCN: 0x0001.95ad6c0e):
Select * From 住院费用记录 Where 病人id = 1023733
Fri Oct 11 11:34:31 2019
ORA-00060: Deadlock detected. More info in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_9744.trc.
ORA-01555 caused by SQL statement below (SQL ID: gmwgdkyndx34j, Query Duration=0 sec, SCN: 0x0001.95ad6c99):
Select * From 住院费用记录 Where 病人id = 1023733
Fri Oct 11 11:34:46 2019
这里的执行时间为0 sec.这里明显感觉不是因为构造CR块的时候undo被覆盖导致的.怀疑可能是bug.于是搜索MOS,发现一篇文件正好一致:
IF: ORA-1555 Reported with Query Duration = 0 , or a Few Seconds (文档 ID 1950577.1)
APPLIES TO:Oracle Database Cloud Service - Version N/A and later
Oracle Database - Standard Edition - Version 11.2.0.1 to 11.2.0.1 [Release 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.SYMPTOMS
a) An incorrect ORA-01555 error is reported with Query Duration=0
e.g
Error message in the alert Log / console will look similar to this:
Wed Jan 4 11:11:56 2014
ORA-01555 caused by SQL statement below (SQL ID: 1dnh4ypr7734w, Query Duration=0 sec, SCN: 0x0000.00be7c41):OR
b) ORA-1555 with (extermely) small query duration reported in the error message. Eg:ORA-01555 caused by SQL statement below (Query Duration=9 sec, SCN: 0x0001.8be5c8d1):
CAUSE
ORA-1555 with Query Duration 0 or a few seconds is mainly caused by indexes/table mismatch Or primary key index corruption.
Similar error is reported for flashback queries or queries involving dblinks.
这里提到了,原因是索引/表不一致,或者主键索引坏块.
结合现实环境,上面的sql语句走病人ID这个索引,于是尝试做一个全表扫描的查询,看是否报错:
Select /*+ FULL(t) */ * From 住院费用记录 t Where t.病人id=1023738;
发现正常.说明表是正常的,但是当通过索引访问数据的时候出现报错,那么定位应该是这个索引有逻辑坏块.
那么解决办法就很简单了,只要重建索引就可以了.由于这个表很大,使用了在线并发重建索引:
alter index XXX rebuild online parallel 8;
别忘了关闭索引的并发度:
alter index XXX noparallel;