Oracle Database Administration I — Question 62

In the ORCL database, UNDOTBS1 is the active undo tablespace with these properties:
1. A size of 100 MB
2. AUTOEXTEND is off
3. UNDO_RETENTION is set to 15 minutes
4. It has RETENTION GUARANTEE
UNDOTBS1 fills with uncommitted undo 10 minutes after the database opens.
What will happen when the next update is attempted by any transaction?

Answer options

• A. It succeeds and the generated undo is stored in SYSTEM.
• B. It fails and returns the error message ג€ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'ג€.
• C. It succeeds and the least recently written undo block of UNDOTBS1 is overwritten by the generated undo.
• D. It succeeds and the generated undo is stored in SYSAUX.
• E. It succeeds and the least recently read undo block of UNDOTBS1 is overwritten by the generated undo.

Correct answer: B

Explanation

The correct answer is B because the UNDOTBS1 tablespace has filled up with uncommitted undo and cannot extend due to AUTOEXTEND being off. Therefore, any further update will fail with the specified error message indicating that it cannot extend the segment due to insufficient space. The other options are incorrect as they suggest successful operations that contradict the limitation imposed by the full undo tablespace.