nerdexam
Oracle

1Z0-052 · Question #216

You started a long transaction. Before committing, you executed a query on one of the tables currently being modified. You received the following error: ORA-01555: snapshot too old How would you…

The correct answer is A. guarantee undo retention D. size the UNDO tablespace appropriately F. configure an appropriate undo retention interval. ORA-01555 (snapshot too old) occurs when undo data required for read consistency has been overwritten; the fix requires ensuring enough undo space, a long enough retention period, and optional guaranteed retention to protect long-running queries.

Managing Undo Data

Question

You started a long transaction. Before committing, you executed a query on one of the tables currently being modified. You received the following error:

ORA-01555: snapshot too old How would you prevent such an error in the future? (Choose three.)

Options

  • Aguarantee undo retention
  • Badd one more redo log group
  • Csize the redo log files appropriately
  • Dsize the UNDO tablespace appropriately
  • Esize the SYSTEM tablespace appropriately
  • Fconfigure an appropriate undo retention interval
  • Gchange automatic undo management to manual

How the community answered

(53 responses)
  • A
    43% (23)
  • B
    32% (17)
  • C
    13% (7)
  • E
    4% (2)
  • G
    8% (4)

Why each option

ORA-01555 (snapshot too old) occurs when undo data required for read consistency has been overwritten; the fix requires ensuring enough undo space, a long enough retention period, and optional guaranteed retention to protect long-running queries.

Aguarantee undo retentionCorrect

Guaranteeing undo retention (setting the RETENTION GUARANTEE clause on the UNDO tablespace) instructs Oracle never to overwrite unexpired undo data even when the tablespace is under space pressure, directly preventing ORA-01555 for long transactions.

Badd one more redo log group

Adding redo log groups affects how quickly Oracle can recycle redo logs and influences checkpoint behavior, but has no effect on how long undo data is retained or available for read-consistent queries.

Csize the redo log files appropriately

Sizing redo log files influences the frequency of log switches and checkpoints but is completely unrelated to undo segment management or the availability of consistent read snapshots.

Dsize the UNDO tablespace appropriatelyCorrect

Sizing the UNDO tablespace appropriately provides enough physical disk space to store undo data for the duration of the longest concurrent transaction or query, reducing the probability that active undo is overwritten before it is no longer needed.

Esize the SYSTEM tablespace appropriately

The SYSTEM tablespace hosts the Oracle data dictionary and system segments; undo data resides in the dedicated UNDO tablespace, so SYSTEM tablespace sizing has no effect on ORA-01555.

Fconfigure an appropriate undo retention intervalCorrect

Configuring an appropriate undo retention interval via the UNDO_RETENTION parameter tells Oracle the minimum number of seconds to retain undo data after a transaction commits, which must be set to at least cover the execution time of the longest running query to prevent snapshot-too-old errors.

Gchange automatic undo management to manual

Switching from Automatic Undo Management (AUM) to manual rollback segment management is a deprecated approach that does not inherently prevent ORA-01555 and removes the benefits of Oracle's automatic undo retention tuning.

Concept tested: Undo retention configuration to prevent ORA-01555

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

Topics

#ORA-01555#undo retention#snapshot too old#undo tablespace sizing

Community Discussion

No community discussion yet for this question.

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