nerdexam
Oracle

1Z0-052 · Question #126

The user SCOTT owns the CUST table that is placed in the SALES tablespace. The user SCOTT opens a session and executes commands as follows: SQL> INSERT INTO cust VALUES(101, 'JACK'); 1 row created…

The correct answer is D. The command hangs until all transactions on the objects in the tablespace commit or rollback, and then. ALTER TABLESPACE ... READ ONLY does not fail or roll back active transactions; it waits until all in-flight transactions on objects in that tablespace finish before completing.

Managing Data and Concurrency

Question

The user SCOTT owns the CUST table that is placed in the SALES tablespace. The user SCOTT opens a session and executes commands as follows:

SQL> INSERT INTO cust VALUES(101, 'JACK'); 1 row created. SQL> INSERT INTO cust VALUES(102, 'SMITH'); 1 row created. As a DBA, you execute the following command from another session:

ALTER TABLESPACE sales READ ONLY; Which statement is true regarding the effect of this command on the transaction in Scott's session?

Options

  • AThe command fails as a transaction is still pending.
  • BThe transaction in Scott's session is rolled back and the tablespace becomes readonly.
  • CThe command waits and the user SCOTT can execute data manipulation language (DML) statements
  • DThe command hangs until all transactions on the objects in the tablespace commit or rollback, and then

How the community answered

(37 responses)
  • A
    8% (3)
  • B
    3% (1)
  • C
    11% (4)
  • D
    78% (29)

Why each option

ALTER TABLESPACE ... READ ONLY does not fail or roll back active transactions; it waits until all in-flight transactions on objects in that tablespace finish before completing.

AThe command fails as a transaction is still pending.

The command does not fail when a transaction is pending; Oracle is designed to handle this gracefully by waiting rather than returning an error.

BThe transaction in Scott's session is rolled back and the tablespace becomes readonly.

Oracle does not automatically roll back Scott's transaction; the DBA command simply waits, leaving the pending transaction under Scott's control.

CThe command waits and the user SCOTT can execute data manipulation language (DML) statements

While the command does wait, Scott cannot issue additional DML statements on tablespace objects during the transition period because Oracle prevents new writes once the READ ONLY transition begins.

DThe command hangs until all transactions on the objects in the tablespace commit or rollback, and thenCorrect

Oracle's ALTER TABLESPACE ... READ ONLY command enters a transitional state where it blocks new DML against the tablespace but waits for any already-open transactions touching objects in that tablespace to commit or roll back. Only after all such transactions complete does the tablespace become read-only, ensuring data consistency without forcibly terminating active work.

Concept tested: ALTER TABLESPACE READ ONLY behavior with active transactions

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ALTER-TABLESPACE.html

Topics

#tablespace read only#DML transactions#concurrent sessions#tablespace state

Community Discussion

No community discussion yet for this question.

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