1Z0-062 · Question #352
Examine the details of the uncompressed, non-partitioned heap table CITIES. Examine the command: SQL> ALTER TABLE cities SHRINK SPACE COMPACT; What must you do before executing it?
The correct answer is C. Enable row movement is enabled. To execute the ALTER TABLE ... SHRINK SPACE COMPACT command, row movement must be explicitly enabled for the table to allow Oracle to relocate rows during the space reclamation process.
Question
Examine the details of the uncompressed, non-partitioned heap table CITIES. Examine the command:
SQL> ALTER TABLE cities SHRINK SPACE COMPACT; What must you do before executing it?
Exhibit
Options
- AEnsure free space that is approximately equal to the space used by the table should be
- BEnsure there are no pending transactions on the table.
- CEnable row movement is enabled.
- DDisable all indexes on the table.
How the community answered
(56 responses)- A2% (1)
- B5% (3)
- C91% (51)
- D2% (1)
Why each option
To execute the `ALTER TABLE ... SHRINK SPACE COMPACT` command, row movement must be explicitly enabled for the table to allow Oracle to relocate rows during the space reclamation process.
While shrinking space reclaims free space, there is no prerequisite to ensure a specific amount of free space exists or is available before executing the command; the database identifies and reclaims it.
The shrink operation can be performed even with pending transactions, though it might wait for necessary locks to be released; it does not require that there are no pending transactions as a precondition.
The `ALTER TABLE ... SHRINK SPACE` command physically reorganizes table data within blocks to reclaim unused space. This operation involves moving individual rows, which necessitates that row movement be enabled for the table using `ALTER TABLE table_name ENABLE ROW MOVEMENT` before the shrink operation can proceed.
Disabling indexes is not required before shrinking a table; Oracle automatically manages and updates indexes during the shrink operation to maintain data integrity.
Concept tested: Oracle table shrink space row movement requirement
Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ALTER-TABLE.html#GUID-5969177F-4D2B-45F4-A971-96531B50D89B__GUID-420C4776-6638-40F1-9656-788225586617
Topics
Community Discussion
No community discussion yet for this question.
