But an under size undo tablespace is not always the case for ora-1555. However the issue manifest with the same error. On the client side as usual ora-1555 is raised as below
java.sql.SQLException: ORA-01555: snapshot too old: rollback segment number 8478 with name "_SYSSMU8478_1180928192$" too smallHowever the key to identify the root cause is the alert log entry associated with the error. In this case several queries failed and alert log had entries similar to following on alert log
ORA-01555 caused by SQL statement below (SQL ID: 194abmtxz7c7t, Query Duration=7 sec, SCN: 0x090d.05f1157b): ORA-01555 caused by SQL statement below (SQL ID: ahrf09nz2mau1, Query Duration=4 sec, SCN: 0x090d.05f12397):They query duration in this case was small. This is the key to identifying if the ora-1555 is due to under size undo tablespace or not. When the query duration is 0 or "small" then this is due to either table/index mismatch or an primary key index corruption (Refer MOS notes at the end of the post for more). Some MOS notes indicate the root cause for this corruption is still not identified as issue cannot be easily reproduced.
If the query duration is large then most likely reasons are undersize undo tablespace or LOB segment corruption where undo is stored inside the LOB (1950897.1, 1580790.1).
Solution is to run a analyze table command with validate structure (use online option if running on a system that is in use) to identify corrupted indexes. Querying the user_indexes view for invalid index will not show any problematic indexes. The analyze command will also raise ora-1555 if it encounter a table with corrupted indexes.
In the above situation ora-1555 was encountered in several SQLs with the same set of tables. So analyze command was only run on the tables used in those queries. Three tables cause ora-1555 to be raised during analysis. As the first step index were rebuild and when that didn't resolve the issue indexes were dropped re-created. Recreating the indexes resolved the issue in this case. It is worth mentioning that the database in question (version : 11.2.0.4 EE) had the parameter db_ultra_safe set to data_and_index. Yet this had not prevented the type of corruption which caused the ora-1555 to be raised.
If recreating the index doesn't resolve then next step is to create a new undo tablespace and switch the database to it. Drop the previously used undo tablespace once all undo segments are offline. It must be said that starting the solution by this step of creating a new undo tablespace and dropping the old didn't resolve the issue. So running the analyze command is the key and this could take a while to complete for large tables (there's no quick solution).
If issue didn't resolve even after creating a new undo tablespace, then raise a SR (refer 1682704.1).
Useful Metalink Notes
Master Note for ORA-1555 Errors [ID 1307334.1] IF: ORA-1555 - Snapshot Too Old Error [ID 1950897.1] How To Size UNDO Tablespace For Automatic Undo Management [ID 262066.1]
Troubleshooting ORA-01555 - Snapshot Too Old: Rollback Segment Number "String" With Name "String" Too Small [ID 1580790.1]
IF: ORA-1555 Reported with Query Duration = 0 , or a Few Seconds [ID 1950577.1]
ORA-01555 When Max Query Length Is Less Than Undo Retention, small or 0 Seconds [ID 1131474.1]
Primary Key Index Corruption Generates ORA-01555 With Small Query Duration or with Query Duration as 0 Seconds [ID 977902.1]
ORA-1555 Error With Query Duration as 0 Seconds [ID 761128.1]
SRDC - ORA-1555: Query Duration 0: Checklist of Evidence to Supply [ID 1682704.1]
