Thursday, May 31, 2007

Segment Shrink in 10g

DML activities like updates and deletes will make segements have chunks of free space within their data blocks.This will make the particular object fragmented
This can lead to very poor performance because select statements that does full table scans will have to scan more blocks to retrieve data.

In oracle 9i, to reclaim free space we can do the following
1.Drop the table,recreate it and then load back the data or
2.Use the 'ALTER TABLE MOVE' command to move the table to a new tablespace
3.Online table reorganization

In Oracle 10g, you are able to shrink segments with commands directly.

During this operation
-Segment data is compacted.
-High-water mark (HWM) is brought down.
-Unused space is released back to the tablespace containing the segment.

e.g
-- Enable row movement.
ALTER TABLE ABU.ORDERS ENABLE ROW MOVEMENT;

-- Recover space and amend the high water mark (HWM).
ALTER TABLE ABU.ORDERS SHRINK SPACE;

-- Recover space, but don't amend the high water mark (HWM).
ALTER TABLE ABU.ORDERS SHRINK SPACE COMPACT;

-- Recover space for the object and all dependant objects.
ALTER TABLE ABU.ORDERS SHRINK SPACE CASCADE;

The shrink is accomplished by moving rows between blocks, hence the requirement for row movement to be enabled for the shrink to take place.
For these operations to work, the objects needs to be in a tablespace with automatic segment-space management enabled.
You need not make a tablespace read-only, offline, or autoextensible before shrinking any of the segments in that tablespace.

No comments: