Q118 In the ORCL database, UNDOTBS1 is the active undo tablespace with these properties:
A size of 100 MB
AUTOEXTEND is off
UNDO_RETENTION is set to 15 minutes
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?
- (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.
Answer: B
To guarantee the success of long-running queries or Oracle Flashback operations, you can enable retention guarantee. If retention guarantee is enabled, the specified minimum undo retention is guaranteed; the database never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace. If retention guarantee is not enabled, the database can overwrite unexpired undo when space is low, thus lowering the undo retention for the system. This option is disabled by default.