1Z0-082 - Oracle Database Administration I Managing Undo Data Questions and Answers — Questions and Answers
Question 1: A long-running report fails with an ORA-01555 "snapshot too old" error. What is the most likely cause?
- The temporary tablespace ran out of space during a sort operation.
- The user running the report lacks the necessary SELECT privileges on the underlying tables.
- The UNDO_RETENTION period is shorter than the query's execution time, causing necessary undo data to be overwritten. (Correct answer)
- The database instance was restarted while the query was executing.
Correct answer: The UNDO_RETENTION period is shorter than the query's execution time, causing necessary undo data to be overwritten.
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)?
- TRANSACTION_CONTROL
- UNDO_MANAGEMENT (Correct answer)
- ROLLBACK_SEGMENTS
- UNDO_TABLESPACE
Correct answer: UNDO_MANAGEMENT
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?
- Set the UNDO_RETENTION parameter to a very high value.
- Enable `RETENTION GUARANTEE` on the active undo tablespace. (Correct answer)
- Create the undo tablespace with the `AUTOEXTEND ON MAXSIZE UNLIMITED` clause.
- Set the `UNDO_MANAGEMENT` parameter to `GUARANTEE`.
Correct answer: Enable `RETENTION GUARANTEE` on the active undo tablespace.
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?
- Archiving redo logs, managing password policies, and storing PL/SQL code.
- Performing checkpoints, writing dirty buffers to disk, and managing the shared pool.
- Transaction rollback, read consistency, and instance recovery. (Correct answer)
- Auditing user activity, enforcing resource limits, and caching data dictionary information.
Correct answer: Transaction rollback, read consistency, and instance recovery.
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?
- V$TRANSACTION
- V$UNDOSTAT (Correct answer)
- V$SESSION_LONGOPS
- DBA_UNDO_EXTENTS
Correct answer: V$UNDOSTAT
`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]
Question 6: When using Automatic Undo Management (AUM), what happens if the instance starts and the undo tablespace specified in the `UNDO_TABLESPACE` parameter is not available?
- The instance will fail to start and report an ORA-01092 error.
- The instance will automatically create a new undo tablespace with a default name.
- The instance will start without an undo tablespace and use the SYSTEM tablespace for undo records. (Correct answer)
- The instance will start in restricted mode, allowing only DBA connections.
Correct answer: The instance will start without an undo tablespace and use the SYSTEM tablespace for undo records.
If the specified undo tablespace is unavailable, or if none is specified and no undo tablespace is available, the instance will still start. However, it will store undo records in the `SYSTEM` tablespace. This is a non-recommended configuration, and an alert will be written to the alert log to warn the DBA. [1, 5]
1Z0-082 - Oracle Database Administration I Managing Undo Data Questions and Answers
A long-running report fails with an ORA-01555 "snapshot too old" error.
What is the most likely cause?