1Z0-052 · Question #95
You execute the following command to change the status of the SALES tablespace: SQL> ALTER TABLESPACE sales OFFLINE; Which statements describe the effect of the command? (Choose all that apply.)
The correct answer is B. A checkpoint is taken on all data files that are associated with the SALES tablespace. C. The sessions that subsequently try to access the objects in the SALES tablespace receivean error. Taking a tablespace offline with the default NORMAL mode checkpoints all associated data files and immediately blocks user access, but requires no media recovery to bring it back online.
Question
You execute the following command to change the status of the SALES tablespace:
SQL> ALTER TABLESPACE sales OFFLINE; Which statements describe the effect of the command? (Choose all that apply.)
Options
- AThe tablespace would require recovery to go back online.
- BA checkpoint is taken on all data files that are associated with the SALES tablespace.
- CThe sessions that subsequently try to access the objects in the SALES tablespace receivean error.
- DThe new status of the SALES tablespace is recorded in the control file when the database instance
How the community answered
(20 responses)- A10% (2)
- B75% (15)
- D15% (3)
Why each option
Taking a tablespace offline with the default NORMAL mode checkpoints all associated data files and immediately blocks user access, but requires no media recovery to bring it back online.
A normal offline checkpoints all data files, leaving them in a consistent state, so no media recovery is required to bring the tablespace back online - recovery is only needed after OFFLINE IMMEDIATE, which skips the checkpoint.
ALTER TABLESPACE ... OFFLINE in default NORMAL mode forces Oracle to perform a checkpoint on all data files in the tablespace, flushing dirty buffers to disk before marking the files offline.
Any session that subsequently attempts to access segments stored in the SALES tablespace receives an error because the data files are no longer accessible to the running instance.
While Oracle does track tablespace status in the control file, the command's primary operational effects are the checkpoint and the blocking of user access, which are the behaviors the question tests.
Concept tested: Oracle tablespace OFFLINE checkpoint and session access behavior
Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-tablespaces.html#GUID-D8C1EB5E-7D85-4B50-A895-8E5B45EEF2D3
Topics
Community Discussion
No community discussion yet for this question.