Login to the Oracle Support portal to access this note and others referred to in this article.

ORA-01555 Explanation

There are two fundamental causes of the error ORA-01555 that are a result of Oracle trying to attain a 'read consistent' image. These are:

• The rollback information itself is overwritten so that Oracle is unable to roll back the (committed) transaction entries to attain a sufficiently old enough version of the block.
• The transaction slot in the rollback segment's transaction table (stored in the rollback segment's header) is overwritten, and Oracle cannot rollback the transaction header sufficiently to derive the original rollback segment transaction slot.

The following solutions are from the Oracle Support Note 269814.1 - ORA-01555 Using Automatic Undo Management - Causes and Solutions (login to the Oracle Support portal to access this note).

1. The UNDO tablespace is too small.
2. Tune the value of the UNDO_RETENTION parameter.
3. Enable retention guarantee for the Undo tablespace.
4. Calculate the size of the UNDO tablespace.

5. Oracle Support Note 1950577.1 - IF: ORA-1555 Reported with Query Duration = 0 , or a Few Seconds
6. Oracle Support Note 846079.1 : LOBs and ORA-01555 troubleshooting and Note 452341.1 : ORA-01555 And Other Errors while Exporting Table With LOBs, How To Detect Lob Corruption 

Please note that all the SQL statements in this section need to be run as SYSDBA, not as the AVUSER account. This is because the tables being accessed and the operations being performed need SYSDBA access. If you are unsure, please consult your Oracle DBA or engage an RSA Support Engineer.

1.  The UNDO tablespace is too small

For this SQL to return valid results, it needs to be run during peak workload; that is, a time similar to when the ORA-01555 error occurred.

1. Run the following command and note the output below:

SELECT (UR * (UPS * DBS)) AS "Bytes" FROM (select max(tuned_undoretention) AS UR from v$undostat),
(SELECT undoblks/((end_time-begin_time)*86400) AS UPS FROM v$undostat WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)),
(SELECT block_size AS DBS FROM dba_tablespaces WHERE tablespace_name = (SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace'));
     Bytes 
---------- 
 269519503

The Undo Tablespace would need to be at least the calculated number of bytes.  However, allow 10-20% when re-sizing or adding data files to the Undo Tablespace.

2. Next, show the sizes and Autoextend setting for the current Data Files used by the Undo Tablespace, along with the output.  Note that the results below do not show a problem.

COL AUTOEXTENSIBLE FORMAT A14
SELECT FILE_NAME, BYTES/1024/1024 AS "BYTES (MB)", AUTOEXTENSIBLE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME=(SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace');
FILE_NAME                                                                        BYTES (MB) AUTOEXTENSIBLE 
-------------------------------------------------------------------------------- ---------- -------------- 
/u01/app/oracle/oradata/AVDB/undotbs01.dbf                                              440 YES 
/u01/app/oracle/oradata/AVDB/undotbs02.dbf                                              128 YES 
/u01/app/oracle/oradata/AVDB/undotbs03.dbf                                              128 YES

3. Add the required space to the Undo Tablespace, as per Oracle Support Note 1951696.1 - IF: How to Resize the Undo Tablespace.  The steps from section 2. Add Space to the Undo Tablespace from Note 1951696.1 have been reproduced here, for your convenience.
   1. To resize the existing undo datafile:

col T_NAME for a23
col FILE_NAME for a65
SELECT tablespace_name T_NAME,file_name, bytes/1024/1024 MB FROM dba_data_files WHERE tablespace_name =(SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace') ORDER BY file_name;

ALTER DATABASE DATAFILE '<COMPLETE_PATH_OF_UNDO_DBF_FILE>' resize <SIZE>M;

ALTER DATABASE DATAFILE 'D:\ORACLE_DB\TESTDB\TESTDB\UNDOTBS01.DBF' RESIZE 1500M;

2. Add a new datafile:

ALTER TABLESPACE <UNDO tbs name> ADD DATAFILE '<COMPLETE_PATH_OF_UNDO_DBF_FILE>' SIZE 300M;

ALTER TABLESPACE UNDOTBS1 ADD DATAFILE 'D:\ORACLE_DB\TESTDB\TESTDB\UNDOTBS02.DBF' SIZE 300M;

2.  Tune the value of the UNDO_RETENTION parameter

The following is from Oracle Support Note 269814.1 - ORA-01555 Using Automatic Undo Management - Causes and Solutions.  It is reproduced here for your convenience.

This is important for systems running long queries. The parameter's value should at least be equal to the length of the longest-running query on a given database instance. This can be determined by querying V$UNDOSTAT view once the database has been running for a while:

SQL> SELECT MAX(maxquerylen) FROM v$undostat;

• When the columns UNXPSTEALCNT through EXPBLKREUCNT holds non-zero values, it is an indication of space pressure.
• If column SSOLDERRCNT is non-zero, then UNDO_RETENTION is not properly set.
• If the column NOSPACEERRCNT is non-zero, then there is a serious space problem.

• Query to determine if UNDO_RETENTION is properly set, where 0 means that no tuning is needed.  

SELECT COUNT(*) as "Tune UNDO_RETENTION" FROM V$UNDOSTAT WHERE SSOLDERRCNT > 0;

If this query returns a non-zero value (count), then it is likely that the UNDO_RETENTION needs to be changed to the maximum value of column v$undostat.maxquerylen (see above).

• Query to determine if there is Space Pressure, where 0 means no.

SELECT COUNT(*) AS "Space Pressure" FROM V$UNDOSTAT WHERE UNXPSTEALCNT > 0 OR UNXPBLKRELCNT > 0 OR UNXPBLKREUCNT > 0 OR EXPSTEALCNT	> 0 OR EXPBLKRELCNT > 0 OR EXPBLKREUCNT > 0;

• Query to determine is there is a serious space problem, where 0 means no.  

SELECT COUNT(*) AS "Serious Space Problem" FROM V$UNDOSTAT WHERE NOSPACEERRCNT > 0;

If this query returns a non-zero value (count), then space should be added to the Undo Tablespace, see Resolution Section 1 (The UNDO tablespace is too small).
 

3.  Enable retention guarantee for the Undo tablespace

There are several Oracle Support notes that explain why this is necessary.  See:

• Oracle Support Note 1100313.1 - Tuned_UndoRetention Can be Less Than Undo_Retention in Init.ora
• Oracle Support Note 1579779.1 - Automatic Tuning of Undo Retention Common Issues

The explanation is "In the event of any undo space constraints, the system will prioritize DML operations over undo retention. In such situations, the low threshold may not be achieved and tuned_undoretention can go below undo_retention.".

So, if you see V$UNDOSTAT.TUNED_UNDORETENTION being less than the UNDO_RETENTION, then setting RETENTION GUARANTEE is recommended by Oracle.
For example:

SQL> SHOW PARAMETER undo_retention
NAME                       TYPE       VALUE
-------------------------- ---------- ------------------------------
undo_retention             integer    900

SQL> SELECT MIN(TUNED_UNDORETENTION) FROM V$UNDOSTAT;
MIN(TUNED_UNDORETENTION)
------------------------
511

This solution means that the Undo data will never be overwritten, where according to the algorithm the Undo Tablespace will instead be extended.

1. Determine the Undo Tablespace name.

SELECT tablespace_name, retention, min_extlen FROM dba_tablespaces WHERE contents = 'UNDO';

2. Using the tablespace name returned by the above query, enable Retention Guarantee on the Undo Tablespace.

ALTER TABLESPACE <tablespace-name> RETENTION GUARANTEE;

4.  Calculate the size of the UNDO tablespace