from:https://community.oracle.com/community/support/support-blogs/database-support-blog/blog/2015/12/10/ora-1555-do-you-know-how-to-resolve-this-issue
Troubleshooting
Below is a list of steps to troubleshoot the issue:
Check Error Messages
Check alert log (or the logfile that contains the error) to determine the details of the error message as there are different types of ORA-1555 error messages:
a. Identify segment name
ORA-01555: snapshot too old: rollback segment number with name “” too small
–> notice that segment name is null “”
or/and
ORA-22924: snapshot too old
In this case 1555 error is reported while accessing UNDO data stored inside LOB segment. ORA-01555 for LOB segment is caused by one of the following reasons :
1. LOB segment corruption:
To check the LOB table for corruption, review the following document :
Document 452341.1 ORA-01555 And Other Errors while Exporting Table With LOBs, How To Detect Lob Corruption
2. If no LOB corruption is found, check for issues with Retention/Pctversion values :
You may need to increase Retention/Pctversion. Review the following document
Document 846079.1 LOBs and ORA-01555 troubleshooting
ORA-01555: snapshot too old: rollback segment number 107 with name “_SYSSMU107_1253191395 ”toosmall−>noticethatinthiscasethesegmentnameexists“SYSSMU1071253191395 ” which means UNDO data inside UNDO tablespace.
In this case, ORA-1555 error is reported while accessing UNDO data at UNDO tablespace which will discuss how to troubleshoot in following steps
b. Identify Query Duration
Failed query duration is logged in some ORA-1555 error messages in the alert log file or an application log.
ORA-01555 caused by SQL statement below (Query Duration=1974 sec, SCN: 0x0002.bc30bcf7):
If the query duration = 0 or few seconds, check the following document
Document 1131474.1 ORA-01555 When Max Query Length Is Less Than Undo Retention, small or 0 Seconds
If the query duration is higher than the UNDO_RETENTION parameter set, increase the UNDO_RETENTION value to meet the query duration.
Ensure to increase the UNDO tablespace size accordingly. If the query duration is equal to or closer to UNDO_RETENTION value, proceed with the following analysis.Check Undo Datafiles
select tablespace_name, file_id, sum(bytes)/1024/1024 a, sum(maxbytes)/1024/1024 b, autoextensible
from dba_data_files
where tablespace_name in
(select tablespace_name from dba_tablespaces where retention like ‘%GUARANTEE’ )
group by file_id, tablespace_name, autoextensible
order by tablespace_name;
If you are using non-autoextensible UNDO data files, this can lead to high calculation of TUNED_UNDORETENTION and hence high allocation
of UNDO space especially with large UNDO data files.
To avoid this make sure that the UNDO data files are autoextensible (with MAXSIZE specified) even if you have enough free space.
NOTE: It is strongly recommended NOT to have both autoextensible and non-autoextensible UNDO data files in the UNDO tablespace as this will
be lead to TUNED_UNDORETENTION mis-calculationCheck TUNED_UNDORETENTION
SQL> select max(maxquerylen),max(tuned_undoretention) from v undostat;SQL>selectmax(maxquerylen),max(tunedundoretention)fromdbahistundostat;a.TUNEDUNDO