Database Reference
In-Depth Information
LOBs and LOB indexes
Materialized views and materialized view logs
Indexes
Partitions and subpartitions
To be eligible for segment shrink, the segment must have row movement enabled and reside
in a tablespace that is locally managed and utilizes Automatic Segment Space Management
(ASSM). The following objects in an ASSM tablespace are not eligible for segment shrink:
SecureFile LOBs
Index-organized table-mapping tables
Tables that have ROWID-based materialized views
Tables with function-based indexes
To enable row movement for a table, issue the ALTER TABLE … ENABLE ROW
MOVEMENT command.
Performing an Online Segment Shrink Operation
Because segment shrink moves rows and changes the ROWIDs, before you perform the
online segment shrink operation, you will need to do the following:
Enable row movement.
Disable any ROWID-based triggers defined on the object.
Determine if the application uses any ROWID-based DML or queries.
By default, online segment shrink performs the following:
Compacts the segment
Resets the high-water mark
Releases the reclaimed free space
Since segment shrink is an online operation, DML and queries can continue as normal.
There is a brief block of concurrent DML operations on the segment when the space is released
at the end of the shrink operation. Indexes remain usable throughout the operation.
Here's an example of shrinking a table:
SQL> ALTER TABLE HR.EMPLOYEES SHRINK SPACE;
ALTER TABLE HR.EMPLOYEES SHRINK SPACE
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled
SQL> ALTER TABLE HR.EMPLOYEES ENABLE ROW MOVEMENT;
Table altered.
Search WWH ::




Custom Search