1Z0-052 · Question #125
Identify three situations in which you would NOT be able to drop a tablespace. (Choose three.)
The correct answer is C. when the tablespace is a default permanent tablespace for the database D. when the tablespace contains a table that is currently being used by transactions E. when the tablespace contains undo data that is needed to rollback an uncommitted transaction. Certain conditions prevent dropping a tablespace: being the default permanent tablespace, having active transactions, or containing undo data needed for rollback.
Question
Identify three situations in which you would NOT be able to drop a tablespace. (Choose three.)
Options
- Awhen the tablespace is online
- Bwhen the tablespace has segments with data in it
- Cwhen the tablespace is a default permanent tablespace for the database
- Dwhen the tablespace contains a table that is currently being used by transactions
- Ewhen the tablespace contains undo data that is needed to rollback an uncommitted transaction
How the community answered
(52 responses)- A10% (5)
- B17% (9)
- C73% (38)
Why each option
Certain conditions prevent dropping a tablespace: being the default permanent tablespace, having active transactions, or containing undo data needed for rollback.
A tablespace being online is not a barrier to dropping it; Oracle allows DROP TABLESPACE on online tablespaces as long as no other blocking conditions exist.
A tablespace that contains data can still be dropped by using the INCLUDING CONTENTS clause, so the mere presence of segments with data is not an absolute restriction.
A tablespace designated as the default permanent tablespace for the database cannot be dropped because Oracle would have no valid target for new user segment creation. You must first reassign the default permanent tablespace to another tablespace before dropping it.
If objects in a tablespace are currently involved in open, uncommitted transactions, Oracle cannot drop the tablespace because doing so would destroy the data blocks still in use by those active transactions, causing data corruption.
Oracle protects undo data that is still needed to support a rollback of an uncommitted transaction. Dropping the tablespace while this undo is in use would make it impossible to roll back the pending transaction, violating ACID guarantees.
Concept tested: Oracle tablespace drop restrictions and prerequisites
Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/DROP-TABLESPACE.html
Topics
Community Discussion
No community discussion yet for this question.