Tales From A Lazy Fat DBA

Reasons:
The ORA-01555 error can occur when a long read only transaction is run against database
and there are many DML transactions being executed on database (on same data).
The longer query runs, there are more chances of encountering ORA-01555 exception.

The ORA-01555 is caused by Oracle “Read Consistency Mechanism”.
Oracle provides read consistency by reading the “before image” of updated data
from “Online UNDO Segments”. If there are lots of updates, long running read-only SQL
and a small UNDO, the ORA-01555 error may encounter.

ORA-01555 can be avoided by following precautions:
1. Database should be in Automatic Undo Management mode.
2. Use a large optimal value for undo_retention.
3. Use a large optimal size of rollback segment (undo) size.
4. Should Commit less often at the time of long running query, to reduce Undo Segment slot reuse.
5. Try to run long running queries on off peak hours, when there is less DML transactions

You can use the retention guarantee parameter (introduced in Oracle 10g) of the create UNDO Tablespace or alter UNDO Tablespace command, as shown in these examples:

-- Assumes OMF is configured.
Create UNDO Tablespace undotbs01
size 200m autoextend on
retention guarantee;

alter tablespace undotbs01 retention guarantee;

Use the alter tablespace retention noguarantee command to reset an UNDO Tablespace so that undo retention is no longer guaranteed.

Example:

— Reset the undo low threshold.
ALTER SYSTEM SET UNDO_RETENTION = 2400;

— Guarantee the minimum threshold is maintained.
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

SELECT tablespace_name, retention FROM dba_tablespaces;

TABLESPACE_NAME                RETENTION
—————————— —————————————————————–
SYSTEM                             NOT APPLY
UNDOTBS1                       GUARANTEE
SYSAUX                            NOT APPLY
TEMP                               NOT APPLY
USERS                             NOT APPLY

5 rows selected.

— Switch back to the default mode.
ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;

TABLESPACE_NAME                RETENTION
—————————— —————————————————————-
SYSTEM                            NOT APPLY
UNDOTBS1                       NOGUARANTEE
SYSAUX                            NOT APPLY
TEMP                               NOT APPLY
USERS                             NOT APPLY

5 rows selected.