Oracle Segment Shrink Space feature for Reclaiming unused space

Apr 9, 2019

Share this post
Learn More
issues-after-qlikview-version-upgrade-on-passive-node-of-production-servers

Introduction:

    • Oracle Segment Shrink Space feature was introduced from Oracle database version 10g.
    • It is used for reclaiming unused/fragmented space from database objects like table, index-organized table (IOT), index, partition, sub-partition, materialized view or materialized view log.
    • Earlier, for reclaiming unused/fragmented space, we export table data then truncate then import the data back to the table. This option requires downtime.
    • Whereas Segment Shrink Space option can be used online where concurrent transactions also allowed.

Why should we reclaim unused/fragmented space from database objects?

    • Deleting rows can create small data gaps that lead to slower table scans.
    • Oracle Database tables with heavily fragmented data waste disk space and can result in moderate to severe performance implications.
    • Oracle Segment Shrink Space option will consolidate the fragmented free space below the high-water mark and compact the data. The high-water mark will be moved back, and the reclaimed space will be released. After the shrink operation, data in the segment will be contained in a smaller number of blocks. This will result in better cache utilization and fewer blocks being read during full table scans.

How to use this feature?

Enable Row Movement:

    • Oracle Segment Shrink Space moves rows between existing blocks to compact the data.
    • So, before we attempt to shrink a table segment we need to enable row movement with the following command.

ALTER TABLE t1 ENABLE ROW MOVEMENT

Segment Shrink Space command:
Recover space and adjust the high-water mark (HWM).
ALTER TABLE t1 SHRINK SPACE;

Segment Shrink Space Options:
COMPACT option:

    • The COMPACT option makes the shrink operation to be performed in two stages. First the rows are moved using the COMPACT option but the high-water mark (HWM) is not adjusted. So parsed SQL statements are not invalidated. The HWM can be adjusted later by reissuing the statement without the COMPACT option. At this point any dependent SQL statements will need to be re-parsed.

Stage 1: Recover space, but don’t adjust the high-water mark (HWM).
ALTER TABLE t1 SHRINK SPACE COMPACT;
Stage 2: Adjust the high-water mark (HWM).
ALTER TABLE t1 SHRINK SPACE;

CASCADE option:

    • The CASCADE option is used for applying this shrink operation on all dependent segments of the object. For example, shrinking a table with CASCADE will also perform segment shrink operation on indexes of that table.

Recover space for the object and all dependent objects.
ALTER TABLE t1 SHRINK SPACE CASCADE;

Restrictions to use this Segment Shrink Space feature:
We need to consider following before performing shrink operations.

    • The shrinking process is only available for objects in tablespaces with automatic segment-space management enabled.
    • Rowid materialized views must be rebuilt after a shrink operation.
    • You can’t shrink a cluster or a clustered table.
    • You can’t shrink any object with a LONG column.
    • You can’t shrink tables with dependent function-based indexes, domain indexes, or bitmap join indexes.
    • You can’t shrink tables that are the master table of an ON COMMIT materialized view.
    • Moving rows can cause a problem with row id based triggers.
    • Shrinks can’t be used for compressed tables, except those using Advanced Row Compression (ROW STORE COMPRESS ADVANCED).

Recent posts

Categories