ora-01555处理

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:

1. 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$” too small -> notice that in this case the segment name exists “_SYSSMU107_1253191395$” 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.

2. 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-calculation

3. Check TUNED_UNDORETENTION
   SQL> select max(maxquerylen),max(tuned_undoretention) from v undostat;SQL>selectmax(maxquerylen),max(tunedundoretention)fromdbahistundostat;a.TUNEDUNDO