View the Exhibit and examine the attributes of an undo tablespace.
In an OLTP system, the user SCOTT has started a query on a large table in the peak transactional hour that performs bulk inserts.
The query runs for more than 15 minutes and then SCOTT receives the following error:
ORA-01555: snapshot too old
What could be the reason for this error?
A.
The query is unable to get a read-consistent image.
B.
There is not enough space in Flash Recovery Area.
C.
There is not enough free space in the flashback archive.
D.
The query is unable to place data blocks in undo tablespace.
Exhibit not showing
ORA-01555: snapshot too old: rollback segment number string with name “string” too small
Cause: rollback records needed by a reader for consistent read are overwritten by other writers
Action: If in Automatic Undo Management mode, increase undo_retention setting. Otherwise, use larger rollback segments
The ORA-01555 is caused by Oracle read consistency mechanism. If you have a long running SQL that starts at 10:30 AM, Oracle ensures that all rows are as they appeared at 10:30 AM, even if the query runs until noon!
Oracles does this by reading the “before image” of changed rows from the online undo segments. If you have lots of updates, long running SQL and too small UNDO, the ORA-01555 error will appear.
From the docs we see that the ORA-01555 error relates to insufficient undo storage or a too small value for the undo_retention parameter:
ORA-01555: snapshot too old: rollback segment number string with name “string” too small
Cause: Rollback records needed by a reader for consistent read are overwritten by other writers.
Action: If in Automatic Undo Management mode, increase the setting of UNDO_RETENTION. Otherwise, use larger rollback segments.