nerdexam
Oracle

1Z0-062 · Question #222

Examine the parameters for a database instance: Your database has three undo tablespaces and the default undo tablespace is not autoextensible. Resumable space allocation is not enabled for any sessio

The correct answer is B. Transactions fail.. When the default undo tablespace is full and resumable space allocation is disabled, any new transaction requiring undo space will fail immediately with ORA-30036.

Managing Undo Data

Question

Examine the parameters for a database instance:

Your database has three undo tablespaces and the default undo tablespace is not autoextensible. Resumable space allocation is not enabled for any sessions in the database instance. What is the effect on new transactions when all undo space in the default undo tablespace is in use by activetransactions?

Exhibit

1Z0-062 question #222 exhibit

Options

  • ATransactions write their undo in the SYSTEM undo segment.
  • BTransactions fail.
  • CTransactions wait until space becomes available in UNDOTBS1.
  • DTransactions write their undo in a temporary tablespace.

How the community answered

(20 responses)
  • A
    5% (1)
  • B
    80% (16)
  • C
    5% (1)
  • D
    10% (2)

Why each option

When the default undo tablespace is full and resumable space allocation is disabled, any new transaction requiring undo space will fail immediately with ORA-30036.

ATransactions write their undo in the SYSTEM undo segment.

In automatic undo management mode, Oracle does not fall back to the SYSTEM undo segment when the dedicated undo tablespace is full; the SYSTEM undo segment is reserved for internal SYSTEM transactions only.

BTransactions fail.Correct

With automatic undo management active and no resumable session configured, Oracle raises ORA-30036 and fails the transaction immediately because there is no wait mechanism or overflow location available - the database has no way to satisfy the undo space request.

CTransactions wait until space becomes available in UNDOTBS1.

Transactions only suspend and wait for space if the session has resumable space allocation enabled via ALTER SESSION ENABLE RESUMABLE, which the question explicitly states is not active.

DTransactions write their undo in a temporary tablespace.

Undo data is never redirected to a temporary tablespace; temporary tablespaces are reserved exclusively for sort operations and global temporary table data.

Concept tested: Oracle automatic undo management space exhaustion behavior

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

Topics

#undo tablespace#transaction management#space allocation

Community Discussion

No community discussion yet for this question.

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