nerdexam
Oracle

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.

Managing Schema Objects

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

1Z0-062 question #352 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)
  • A
    2% (1)
  • B
    5% (3)
  • C
    91% (51)
  • D
    2% (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.

AEnsure free space that is approximately equal to the space used by the table should be

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.

BEnsure there are no pending transactions on the table.

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.

CEnable row movement is enabled.Correct

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.

DDisable all indexes on the table.

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

#table management#SHRINK SPACE#row movement

Community Discussion

No community discussion yet for this question.

Full 1Z0-062 Practice