Shrink
Shrink
Shrink
1.0
Contents
1 Overview....................................................................................................3
Objects with fragmented free space can result in much wasted space, and
can impact database performance. The preferred way to defragment and
reclaim this space is to perform an online segment shrink. This process
consolidates fragmented free space below the high water mark and
compacts the segment. After compaction, the high water mark is moved,
resulting in new free space above the high water mark. That space above
the high water mark is then deallocated. The segment remains available
for queries and DML during most of the operation and no extra disk space
need be allocated.
You use the Segment Advisor to identify segments that would benefit from
online segment shrink. Only segments in locally managed tablespaces
with automatic segment space management (ASSM) are eligible.
If a table with reclaimable space is not eligible for online segment shrink,
or if you want to make changes to logical or physical attributes of the
table while reclaiming space, you can use online table redefinition as an
alternative to segment shrink.
2 Using the Segment Advisor
The Segment Advisor identifies segments that have space available for
reclamation. It performs its analysis by examining usage and growth
statistics in the Automatic Workload Repository (AWR), and by sampling
the data in the segment. It is configured to run during maintenance
windows as an automated maintenance task, and you can also run it on
demand (manually). The Segment Advisor automated maintenance task is
known as the Automatic Segment Advisor.
You use online segment shrink to reclaim fragmented free space below
the high water mark in an Oracle Database segment. The benefits of
segment shrink are these:
Segment shrink reclaims unused space both above and below the high
water mark. In contrast, space deallocation reclaims unused space only
above the high water mark. In shrink operations, by default, the database
compacts the segment, adjusts the high water mark, and releases the
reclaimed space.
This looks like alter table ... move command, but actually it is different in
Oracle 10g.
3.1.1 alter table ... shrink space VS. alter table ... move
Alter table SIEBEL.S_EVT_ACT move;
ROWNUM
----------
1
The difference between shrink command and move command is that the
shrink command does not lock the object in exclusive mode.
move command is executed with LOCKED_MODE=6 (exclusive mode).
shrink command, on the other hand, is executed with LOCKED_MODE=3
(row lock mode), which enables recovering without stopping operations.
Segment is shrunk even though the command is stopped in the middle of
the execution.
4 Deallocating Unused Space
When you deallocate unused space, the database frees the unused space
at the unused (high water mark) end of the database segment and makes
the space available for other segments in the tablespace.