Oracle
1Z0-062 · Question #222
1Z0-062 Question #222: Real Exam Question with Answer & Explanation
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.
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
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.
Explanation
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.
Common mistakes.
- A. 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.
- C. 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.
- D. 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
Reference. https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-undo.html
Community Discussion
No community discussion yet for this question.