ORA-1555 Reported with Query Duration = 0 , or a Few Seconds

检查alert发现ORA-01555报错确实和下文描述的很像

ORA-01555 caused by SQL statement below(SQL ID: 9ggx5zvjkgd1t, Query Duration=0 sec, SCN: 0x0570.57568308):

IF: ORA-1555 Reported with Query Duration = 0 , or a Few Seconds (文档 ID 1950577.1)

转到底部

---

In this Document Symptoms   Cause   Solution   Related Issues:   References APPLIES TO: Oracle Database - Enterprise Edition - Version 9.2.0.1 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. 相似的错误也在闪回查询和dblink查询中出现 SOLUTION 解决办法 1. Find the failing SQL statement and the tables involved. 找到出问题的SQL但牵扯到一个问题就是sql中的表属于不同的用户，如何确定应该用哪个用户 1.a) Check the error logged in the alert log file. Along with the error message, the failing statement is also reported. 1.b) If no sql query is shown along with ORA-1555 error, then get the SQL ID from the error message (reported in the console / alert log file) and then find the SQL statement as below: 1.b.i) If the Database was not restarted after the error ORA-1555 , so the Statement can be obtained from : SQL> select SQL_TEXT from SQL_TEXT where SQL_ID='<sql id from the error message>'; 1.b.ii)If the Database was restarted after the error ORA-1555 and an AWR snapshot was gathered before the restart , so the Statement can be obtained from : SQL> select SQL_TEXT from DBA_HIST_SQLTEXT where SQL_ID='<sql id from the error message>'; 2) This step is specific to flashback queries and queries involving dblinks. If your situation doesn't match, please skip this step and go to step 3.本步是关于闪回和dblink的不用看 If the flashback query is failing with ORA-1555 error, ensure the UNDO_RETENTION is set for the duration specified in the query. If not, set the UNDO_RETENTION accordingly and retry the failing query. If the failing query involve dblinks, ensure the UNDO_RETENTION is set high enough on both source and target databases. 3.) Once the tables in the failing query is identified, execute analyze statement to check for possible inconsistencies.      这一步就牵扯到刚才提到的用户问题      SQL> analyze table PS_SGC_JOB validate structure cascade;                analyze table PS_SGC_JOB validate structure cascade                      *                ERROR at line 1:                ORA-00942: table or view does not exist SQL>analyze table <table name found in the error message> validate structure cascade and check if you are getting the same ORA-1555 error. 4) Resolve the inconsistency by recreating the indexes associated with the table. 4.a) Rebuild/Recreate the Indexes associated with the table. You can either rebuild the indexes:   alter index <index name> rebuild online; In some cases, rebuilding wont help and may have to recreate the indexes. Get the DDL of the indexes using DBMS_METADA.GET_DDL   select dbms_metadata.get_ddl('INDEX','<index name>','<schema>') from dual; Drop the indexes and then recreate using the DDL obtained in the above step. 4.b) If it is Primary Key Index then disable and enable the PK constraint in order to recreate the PK index associated with the primary key constraint and to solve the primary key index corruption. E.g : SQL> alter table <table name> disable primary key; SQL> alter table <table name> enable primary key; 4.c) If there are many indexes associated with the table, check the execution plan to see the indexes involved in the failing query and recreate them 5) If the issue still persists, then create a new undo table space and switch the Undo to the new tablespace 5.a) Create a new undo tablespace with new name SQL> create undo tablespace <New Undo Tablespace name> size <new size>; 5.b) Set the new tablespace as the undo tablespace to be used: SQL> alter system set undo_tablespace=<New Undo Tablespace name>; 5.c) Donot drop the old Undo tablespace immediately. Check the status of the Undo segments in the Undo tablespace to be dropped.   select tablespace_name , status , count(*) from dba_rollback_segs group by tablespace_name , status; If there are Undo segments with status other than OFFLINE in the tablespace to be dropped, we need to wait till they become OFFLINE.   select status,segment_name from dba_rollback_segs where status not in ("OFFLINE') and tablespace_name=<undo tablespace to be dropped>; If all the Undo segments in the tablespace to the dropped is of status OFFLINE, then drop the tablespace.   SQL>Drop tablespace <tablespace_name> including contents and datafiles; For more details refer to the troubleshooting guide: Troubleshooting ORA-01555 - Snapshot Too Old: Rollback Segment Number "String" With Name "String" Too Small (Doc ID 1580790.1)   Related Issues:   If the ORA-1555 error is reported on CLOB or BLOB data , refer to Doc ID 1950896.1   If the ORA-1555 error message reports an unrealistic high Query Duration (which is far higher than the actual query duration), refer to Doc ID 1950961.1   For ORA-1555 errors reported on Export, refer to documents Doc ID 1950937.1 and Doc ID 1950978.1. They explains the solution with and without LOB data, respectively.   For other cases where the ORA-1555 is reported refer to the document Doc ID 1950897.1