Oracle Database 12c: Installation and Administration — Question 79

One of your databases supports an OLTP workload.
The default UNDO tablespace is fixed size with:
1. RETENTION NOGUARANTEE
2. UNDO_RETENTION is 12 minutes
User SCOTT gets this error after a query on the SALES table has run for more than 15 minutes:

ORA-01555: snapshot too old -
Which three factors taken separately or in some combination might be the cause? (Choose three.)

Answer options

• A. An update was made to the SALES table after the query began
• B. An update to the SALES table was committed after the query began
• C. A committed delete to the SALES table was made more than 12 minutes before the query began
• D. An uncommitted update to the SALES table was made more than 12 minutes before the query began
• E. A committed update to the SALES table was made more than 12 minutes before the query began
• F. An uncommitted delete to the SALES table was made more than 12 minutes before the query began
• G. An update was made to the SALES table before the query began

Correct answer: A, D, E

Explanation

The ORA-01555 error indicates that a query attempted to access an older version of data that was already overwritten in the UNDO tablespace. Option A is correct because any update after the query begins can lead to this issue. Option D is correct because uncommitted changes that exceed the UNDO retention period can cause a snapshot too old error. Option E is also correct because a committed update that occurred before the query started would have aged out of the UNDO tablespace. The other options do not directly contribute to the snapshot being too old.