Oracle Database 12c: Installation and Administration — Question 46

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?

Answer options

• A. The command fails as a transaction is still pending.
• B. The transaction in Scott's session is rolled back and the tablespace becomes readonly.
• C. The command waits and the user SCOTT can execute data manipulation language (DML) statements only as part of the current transaction.
• D. The command hangs until all transactions on the objects in the tablespace commit or rollback, and then the tablespace is placed in readonly mode.

Correct answer: D

Explanation

The correct answer is D because the ALTER TABLESPACE command will wait until all transactions on the objects within the tablespace are completed before it can change the state of the tablespace to readonly. Options A, B, and C are incorrect as they do not accurately describe the behavior of the command in relation to ongoing transactions.