nerdexam
Oracle

1Z0-052 · Question #56

Your database instance is configured with automatic undo management and the UNDO_RETENTION parameter is set to 900 seconds. You executed the following command to enable retention guarantee: SQL>…

The correct answer is B. The extents containing committed undo in the undo tablespace are not overwritten for at least 15 minutes. RETENTION GUARANTEE forces Oracle to honour the full UNDO_RETENTION period for committed undo, preventing overwrite even when the undo tablespace is under space pressure.

Managing Undo Data

Question

Your database instance is configured with automatic undo management and the UNDO_RETENTION parameter is set to 900 seconds. You executed the following command to enable retention guarantee:

SQL> ALTER TABLESPACE undotbs1 RETENTION GUARANTEE; What affect would this command have on the database?

Options

  • AThe extents in the undo tablespace retain data until the next full database backup
  • BThe extents containing committed undo in the undo tablespace are not overwritten for at least 15 minutes
  • CThe extents containing committed data in the undo tablespace are not overwritten until the instance
  • DThe extents containing committed undo in the undo tablepace are transferred to Flash Recovery Area

How the community answered

(31 responses)
  • A
    3% (1)
  • B
    81% (25)
  • C
    10% (3)
  • D
    6% (2)

Why each option

RETENTION GUARANTEE forces Oracle to honour the full UNDO_RETENTION period for committed undo, preventing overwrite even when the undo tablespace is under space pressure.

AThe extents in the undo tablespace retain data until the next full database backup

Undo retention is governed purely by the time-based UNDO_RETENTION parameter in seconds and has no relationship to full database backup schedules.

BThe extents containing committed undo in the undo tablespace are not overwritten for at least 15 minutesCorrect

With UNDO_RETENTION=900 seconds (15 minutes) and RETENTION GUARANTEE set on undotbs1, Oracle will not overwrite extents holding committed undo data until at least 900 seconds have elapsed. Without GUARANTEE, Oracle may reclaim committed undo space prematurely if active transactions need room, but GUARANTEE enforces the retention floor unconditionally, reducing ORA-01555 snapshot-too-old errors.

CThe extents containing committed data in the undo tablespace are not overwritten until the instance

RETENTION GUARANTEE does not hold undo until instance shutdown - it holds committed undo for the duration specified by UNDO_RETENTION (900 seconds), after which the space becomes eligible for reuse.

DThe extents containing committed undo in the undo tablepace are transferred to Flash Recovery Area

Committed undo data is never transferred to the Flash Recovery Area; it stays within the designated undo tablespace until the retention period expires and space is reclaimed.

Concept tested: Undo retention guarantee and UNDO_RETENTION parameter

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-undo.html

Topics

#undo retention#RETENTION GUARANTEE#undo tablespace#committed undo

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 1Z0-052 Practice