Oracle Reclaiming Unused Space

Oracle Tips by Burleson Consulting

This is an excerpt from "Oracle 10g New Features for Administrators" by Ahmed Baraka.

In Oracle Database 10g, you can use the new segment shrinking capability to make sparsely populated segments give their space back to their parent tablespace.

Restrictions on Shrinking Segments

� You can only shrink segments that use Automatic Segment Space Management.

� You must enable row movement for heap-organized segments. By default, row movement is disabled at the segment level.

ALTER TABLE test ENABLE ROW MOVEMENT;

� You can�t shrink the following:

o Tables that are part of a cluster
o Tables with LONG columns,
o Certain types of materialized views
o Certain types of IOTs.
o Tables with function-based indexes.

� In Oracle 10.2 you can also shrink:

o LOB Segments
o Function Based Indexes
o IOT Overflow Segments

Segment Shrinking Phases

There are two phases in a segment-shrinking operation:

Compaction phase

During this phase, the rows in a table are compacted and moved toward the left side of the segment and you can issue DML statements and queries on a segment while it is being shrunk.

Adjustment of the HWM/releasing space phase

During the second phase, Oracle lowers the HWM and releases the recovered free space under the old HWM to the parent tablespace. Oracle locks the object in an exclusive mode.

Manual Segment Shrinking

Manual Segment Shrinking is done by the statement:

ALTER TABLE test SHRINK SPACE

You can shrink all the dependent segments as well:

ALTER TABLE test SHRINK SPACE CASCADE

To only compact the space in the segment:

ALTER TABLE test SHRINK SPACE COMPACT

To shrinks a LOB segment:

ALTER TABLE employees MODIFY LOB(resume)
(SHRINK SPACE)

To shrink an IOT overflow segment belonging to the EMPLOYEES table:

ALTER TABLE employees OVERFLOW SHRINK SPACE

Shrinking Segments Using the Database Control

To enable row movement:

Follow the links: Schema, Tables, Edit Tables, then Options.

To shrink a table segment:

Follow the links: Schema, Tables, select from the Actions field Shrink Segments and click Go.

Using the Segment Advisor - Choosing Candidate Objects for Shrinking

The Segment Advisor, to estimate future segment space needs, uses the growth trend report based on the AWR space-usage data.

Follow the links:

Database Home page, Advisor Central in the Related

Links, Segment Advisor.

Automatic Segment Advisor

Automatic Segment Advisor is implemented by the AUTO_SPACE_ADVISOR_JOB job. This job executes the DBMS_SPACE.AUTO_SPACE_ADVISOR_JOB_PROC procedure at predefined points in time.

When a Segment Advisor job completes, the job output contains the space problems found and the advisor recommendations for resolving those problems.

You can view all Segment Advisor results by navigating to the Segment Advisor Recommendations page. You access this page from the home page by clicking the Segment Advisor Recommendations link in the Space Summary section.

The following views display information specific to Automatic Segment Advisor:

o DBA_AUTO_SEGADV_SUMMARY: Each row of this view summarizes one Automatic Segment Advisor run. Fields include number of tablespaces and segments processed, and number of recommendations made.

o DBA_AUTO_SEGADV_CTL: This view contains control information that Automatic Segment Advisor uses to select and process segments.