1Z0-082 practice question 12 of 25

Which two are true about shrinking a segment online? (Choose two.)

Full set: 142 questions
Answer options
A: It is not possible to shrink either indexes or Index Organized Tables (IOTs)
B: It always eliminates all migrated rows if any exist in the table
C: To shrink a table it must have a PRIMARY KEY constraint
D: To shrink a table it must have a UNIQUE KEY constraint
E: To shrink a table it must have row movement enabled Correct answer
F: It must be in a tablespace that uses Automatic Segment Space Management (ASSM) Correct answer
Correct Answer:
E. To shrink a table it must have row movement enabled; F. It must be in a tablespace that uses Automatic Segment Space Management (ASSM)
Explanation

Online segment shrinking is a feature used to reclaim fragmented free space below the high water mark of a segment. For this operation to be possible, two main prerequisites must be met. First, the segment must reside in a tablespace that is managed with Automatic Segment Space Management (ASSM). Second, because the shrink operation compacts data by moving rows to new locations, row movement must be explicitly enabled on the target table using the ALTER TABLE ... ENABLE ROW MOVEMENT command.

Why Incorrect

A. It is possible to shrink indexes and Index-Organized Tables (IOTs), not just heap-organized tables.
B. While shrinking is effective at eliminating migrated rows by moving them, using the word "always" is too strong; its primary goal is space reclamation.
C. A PRIMARY KEY constraint is not a prerequisite for shrinking a table segment.
D. A UNIQUE KEY constraint is not a prerequisite for shrinking a table segment.

References

1. Oracle Database Administrator's Guide, 19c. Chapter 21, "Managing Space for Schema Objects". Section: "Reclaiming Wasted Space". It states, "You can shrink space in a table, index-organized table, index, partition, subpartition, materialized view, or materialized view log" and "Segment shrink is not supported for segments in tablespaces that are not locally managed, or for segments in locally managed tablespaces that do not use automatic segment space management (ASSM)."

2. Oracle Database SQL Language Reference, 19c. ALTER TABLE command, shrinkclause. The documentation notes: "This clause is valid only for segments in tablespaces with automatic segment management." and "Before you issue this statement...you must enable row movement in the table you want to shrink."

Scroll to Top