1Z0-082 - Oracle Database Administration I Managing Undo Data Questions and Answers

Question 1

A long-running report fails with an ORA-01555 "snapshot too old" error. What is the most likely cause?

Accepted Answer

The UNDO_RETENTION period is shorter than the query's execution time, causing necessary undo data to be overwritten.

Answer

The ORA-01555 error occurs when a query requires a version of a data block to maintain read consistency, but that version is no longer available in the undo tablespace. This typically happens when the undo information has been overwritten by newer transactions because the query's duration exceeded the configured UNDO_RETENTION period. [8, 10, 21]

Question 2

Which initialization parameter must be set to 'AUTO' to enable Automatic Undo Management (AUM)?

Accepted Answer

UNDO_MANAGEMENT

Answer

The `UNDO_MANAGEMENT` parameter controls the undo mode for the instance. Setting it to `AUTO` enables Automatic Undo Management, where the database transparently manages undo segments within an undo tablespace. The legacy `MANUAL` mode requires manual DBA management of rollback segments. [4, 5, 6]

Question 3

A database administrator needs to ensure that unexpired undo data is never overwritten, even if it means subsequent DML operations that require undo space will fail. Which action accomplishes this?

Accepted Answer

Enable `RETENTION GUARANTEE` on the active undo tablespace.

Answer

By default, `UNDO_RETENTION` is a target, not a strict guarantee. To prevent unexpired undo from being overwritten, you must enable `RETENTION GUARANTEE` on the undo tablespace using the `ALTER TABLESPACE ... RETENTION GUARANTEE` command. This forces the database to honor the retention period, causing new DML to fail if space runs out, rather than overwriting needed undo data. [1, 2, 16]

Question 4

Which of the following are the three primary purposes of undo data in an Oracle database?

Accepted Answer

Transaction rollback, read consistency, and instance recovery.

Answer

Undo data is essential for three core database functions: 1) Rolling back uncommitted transactions (e.g., via a `ROLLBACK` statement), 2) Providing read consistency for queries so they see a consistent version of the data as it existed when the query began, and 3) Rolling back uncommitted changes during instance recovery after a crash. [14, 15, 22]

Question 5

A DBA needs to analyze historical undo generation rates and identify the longest-running queries over the past few days to properly size the undo tablespace. Which dynamic performance view is best suited for this task?

Accepted Answer

V$UNDOSTAT

Answer

`V$UNDOSTAT` collects statistics on undo space usage over 10-minute intervals. It is the primary tool for monitoring undo generation (`UNDOBLKS`), transaction counts (`TXNCOUNT`), and identifying the duration of the longest query (`MAXQUERYLEN`) within each interval, making it ideal for tuning `UNDO_RETENTION` and sizing the undo tablespace. [2, 9, 20]

1Z0-082 - Oracle Database Administration I Practice Test

1Z0-082 - Oracle Database Administration I Practice Test

1Z0-082 - Oracle Database Administration I Managing Undo Data Questions and Answers