nerdexam
Oracle

1Z0-052 · Question #202

View the Exhibit and examine the undo tablespace attributes. Your database instance is experiencing a large volume of transactions from non-DBA users in the last one hour. The undo tablespace…

The correct answer is A. The DML commands will fail. When the active undo tablespace is full and no committed undo data is available to overwrite, new DML operations fail because Oracle can use only one undo tablespace at a time.

Managing Undo Data

Question

View the Exhibit and examine the undo tablespace attributes. Your database instance is experiencing a large volume of transactions from non-DBA users in the last one hour. The undo tablespace NDOTBS1 is full with transactions and no transaction was committed more than one hour ago. The database has two more undo tablespaces. What happens to the new DML operations in this scenario?

Exhibit

1Z0-052 question #202 exhibit

Options

  • AThe DML commands will fail
  • BThe undo data generated by the DML is stored in one of the two other undo tablespace
  • CThe undo data generated by the DML will overwrite the extents that contain committed undo data
  • DThe undo data generated by the DML is stored in the SYSTEM undo segment of the SYSTEM tablespace

How the community answered

(51 responses)
  • A
    71% (36)
  • B
    18% (9)
  • C
    8% (4)
  • D
    4% (2)

Why each option

When the active undo tablespace is full and no committed undo data is available to overwrite, new DML operations fail because Oracle can use only one undo tablespace at a time.

AThe DML commands will failCorrect

Oracle's UNDO_TABLESPACE parameter designates a single active undo tablespace per instance at any time; the two additional undo tablespaces are inactive and cannot absorb new undo data automatically. Because every transaction in NDOTBS1 is uncommitted and therefore unexpired, no extents can be reused, and new DML statements receive ORA-30036 (unable to extend segment in undo tablespace), causing them to fail.

BThe undo data generated by the DML is stored in one of the two other undo tablespace

Oracle does not transparently redirect undo data to a standby undo tablespace; only the tablespace specified by UNDO_TABLESPACE is active, and switching requires a manual ALTER SYSTEM SET UNDO_TABLESPACE command.

CThe undo data generated by the DML will overwrite the extents that contain committed undo data

Overwriting extents containing committed undo data is only possible when those extents are past the undo retention threshold; since no transaction was committed more than one hour ago, all undo is unexpired and cannot be overwritten.

DThe undo data generated by the DML is stored in the SYSTEM undo segment of the SYSTEM tablespace

The SYSTEM undo segment is reserved strictly for recursive SQL issued by the database itself and is not available as a fallback for user transaction undo data.

Concept tested: Oracle undo tablespace capacity and single active undo tablespace behavior

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

Topics

#undo tablespace#undo retention#DML failure#undo management

Community Discussion

No community discussion yet for this question.

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