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