Reclaiming Wasted Space

This section explains how to reclaim wasted space, and also introduces the Segment Advisor, which is the Oracle Database component that identifies segments that have space available for reclamation. The following topics are covered:

Understanding Reclaimable Unused Space

Over time, updates and deletes on objects within a tablespace can create pockets of empty space that individually are not large enough to be reused for new data. This type of empty space is referred to as fragmented free space.

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. Other restrictions on segment type exist. For more information, see "Shrinking Database Segments Online".

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. Online redefinition is also referred to as reorganization. Unlike online segment shrink, it requires extra disk space to be allocated. See "Redefining Tables Online" for more information.

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.

The Segment Advisor generates the following types of advice:

  • If the Segment Advisor determines that an object has a significant amount of free space, it recommends online segment shrink. If the object is a table that is not eligible for shrinking, as in the case of a table in a tablespace without automatic segment space management, the Segment Advisor recommends online table redefinition.

  • If the Segment Advisor encounters a table with row chaining above a certain threshold, it records that fact that the table has an excess of chained rows.

    Note:

    The Segment Advisor flags only the type of row chaining that results from updates that increase row length.

If you receive a space management alert, or if you decide that you want to reclaim space, you should start with the Segment Advisor.

To use the Segment Advisor:

  1. Check the results of the Automatic Segment Advisor.

    To understand the Automatic Segment Advisor, see "Automatic Segment Advisor", later in this section. For details on how to view results, see "Viewing Segment Advisor Results".

  2. (Optional) Obtain updated results on individual segments by rerunning the Segment Advisor manually.

    See "Running the Segment Advisor Manually", later in this section.

Automatic Segment Advisor

The Automatic Segment Advisor is an automated maintenance task that is configured to run during all maintenance windows.

The Automatic Segment Advisor does not analyze every database object. Instead, it examines database statistics, samples segment data, and then selects the following objects to analyze:

  • Tablespaces that have exceeded a critical or warning space threshold

  • Segments that have the most activity

  • Segments that have the highest growth rate

If an object is selected for analysis but the maintenance window expires before the Segment Advisor can process the object, the object is included in the next Automatic Segment Advisor run.

You cannot change the set of tablespaces and segments that the Automatic Segment Advisor selects for analysis. You can, however, enable or disable the Automatic Segment Advisor task, change the times during which the Automatic Segment Advisor is scheduled to run, or adjust automated maintenance task system resource utilization. See "Configuring the Automatic Segment Advisor" for more information.

Running the Segment Advisor Manually

You can manually run the Segment Advisor at any time with Enterprise Manager or with PL/SQL package procedure calls. Reasons to manually run the Segment Advisor include the following:

  • You want to analyze a tablespace or segment that was not selected by the Automatic Segment Advisor.

  • You want to repeat the analysis of an individual tablespace or segment to get more up-to-date recommendations.

You can request advice from the Segment Advisor at three levels:

  • Segment level—Advice is generated for a single segment, such as an unpartitioned table, a partition or subpartition of a partitioned table, an index, or a LOB column.

  • Object level—Advice is generated for an entire object, such as a table or index. If the object is partitioned, advice is generated on all the partitions of the object. In addition, if you run Segment Advisor manually from Enterprise Manager, you can request advice on the object's dependent objects, such as indexes and LOB segments for a table.

  • Tablespace level—Advice is generated for every segment in a tablespace.

The OBJECT_TYPE column of Table 17-2 shows the types of objects for which you can request advice.

Running the Segment Advisor Manually with Enterprise Manager

You must have the OEM_ADVISOR role to run the Segment Advisor manually with Enterprise Manager. There are two ways to run the Segment Advisor:

  • Using the Segment Advisor Wizard

    This method enables you to request advice at the tablespace level or object level. At the object level, you can request advice on tables, indexes, table partitions, and index partitions. Dependent objects such as LOB segments cannot be included in the analysis.

  • Using the Run Segment Advisor command on a schema object page.

    For example, if you display a list of tables on the Tables page (accessible from the Schema page), you can select a table and then select the Run Segment Advisor command from the Actions menu.

    This method enables you to include the schema object's dependent objects in the Segment Advisor run. For example, if you select a table and select the Run Segment Advisor command, Enterprise Manager displays the table's dependent objects, such as partitions, index segments, LOB segments, and so on. You can then select dependent objects to include in the run.

In both cases, Enterprise Manager creates the Segment Advisor task as an Oracle Database Scheduler job. You can schedule the job to run immediately, or can take advantage of advanced scheduling features offered by the Scheduler.

To run the Segment Advisor manually with the Segment Advisor Wizard:

  1. From the database Home page, under Related Links, click Advisor Central.

    The Advisor Central page appears. (See Figure 17-2.)

  2. Under Advisors, click Segment Advisor.

    The first page of the Segment Advisor wizard appears.

  3. Follow the wizard steps to schedule the Segment Advisor job, and then click Submit on the final wizard page.

    The Advisor Central page reappears, with the new Segment Advisor job at the top of the list under the Results heading. The job status is SCHEDULED or RUNNING. (If you do not see your job, use the search fields above the list to display it.)

  4. Check the status of the job. If it is not COMPLETED, click the Refresh button at the top of the page repeatedly. (Do not use your browser's Refresh icon.)

    When the job status changes to COMPLETED, select the job by clicking in the Select column, and then click View Result.

    Figure 17-2 Advisor Central page

    Description of Figure 17-2 follows
    Description of "Figure 17-2 Advisor Central page"

See Also:

Chapter 27, "Scheduling Jobs with Oracle Scheduler" for more information about the advanced scheduling features of the Scheduler.

Running the Segment Advisor Manually with PL/SQL

You can also run the Segment Advisor with the DBMS_ADVISOR package. You use package procedures to create a Segment Advisor task, set task arguments, and then execute the task. You must have the ADVISOR privilege. Table 17-1 shows the procedures that are relevant for the Segment Advisor. Please refer to Oracle Database PL/SQL Packages and Types Reference for more details on these procedures.

Table 17-1 DBMS_ADVISOR package procedures relevant to the Segment Advisor

Package Procedure Name Description

CREATE_TASK

Use this procedure to create the Segment Advisor task. Specify 'Segment Advisor' as the value of the ADVISOR_NAME parameter.

CREATE_OBJECT

Use this procedure to identify the target object for segment space advice. The parameter values of this procedure depend upon the object type. Table 17-2 lists the parameter values for each type of object.

Note: To request advice on an IOT overflow segment, use an object type of TABLE, TABLE PARTITION, or TABLE SUBPARTITION. Use the following query to find the overflow segment for an IOT and to determine the overflow segment table name to use with CREATE_OBJECT:

select table_name, iot_name, iot_type from dba_tables;

SET_TASK_PARAMETER

Use this procedure to describe the segment advice that you need. Table 17-3 shows the relevant input parameters of this procedure. Parameters not listed here are not used by the Segment Advisor.

EXECUTE_TASK

Use this procedure to execute the Segment Advisor task.


Table 17-2 Input for DBMS_ADVISOR.CREATE_OBJECT

Input Parameter
OBJECT_TYPE ATTR1 ATTR2 ATTR3 ATTR4

TABLESPACE

tablespace name

NULL

NULL

Unused. Specify NULL.

TABLE

schema name

table name

NULL

Unused. Specify NULL.

INDEX

schema name

index name

NULL

Unused. Specify NULL.

TABLE PARTITION

schema name

table name

table partition name

Unused. Specify NULL.

INDEX PARTITION

schema name

index name

index partition name

Unused. Specify NULL.

TABLE SUBPARTITION

schema name

table name

table subpartition name

Unused. Specify NULL.

INDEX SUBPARTITION

schema name

index name

index subpartition name

Unused. Specify NULL.

LOB

schema name

segment name

NULL

Unused. Specify NULL.

LOB PARTITION

schema name

segment name

lob partition name

Unused. Specify NULL.

LOB SUBPARTITION

schema name

segment name

lob subpartition name

Unused. Specify NULL.


Table 17-3 Input for DBMS_ADVISOR.SET_TASK_PARAMETER

Input Parameter Description Possible Values Default Value

time_limit

The time limit for the Segment Advisor run, specified in seconds.

Any number of seconds

UNLIMITED

recommend_all

Whether the Segment Advisor should generate findings for all segments.

TRUE: Findings are generated on all segments specified, whether or not space reclamation is recommended.

FALSE: Findings are generated only for those objects that generate recommendations for space reclamation.

TRUE


Example The example that follows shows how to use the DBMS_ADVISOR procedures to run the Segment Advisor for the sample table hr.employees. The user executing these package procedures must have the EXECUTE object privilege on the package or the ADVISOR system privilege.

Note that passing an object type of TABLE to DBMS_ADVISOR.CREATE_OBJECT amounts to an object level request. If the table is not partitioned, the table segment is analyzed (without any dependent segments like index or LOB segments). If the table is partitioned, the Segment Advisor analyzes all table partitions and generates separate findings and recommendations for each.

variable id number;
begin
  declare
  name varchar2(100);
  descr varchar2(500);
  obj_id number;
  begin
  name:='Manual_Employees';
  descr:='Segment Advisor Example';

  dbms_advisor.create_task (
    advisor_name     => 'Segment Advisor',
    task_id          => :id,
    task_name        => name,
    task_desc        => descr);

  dbms_advisor.create_object (
    task_name        => name,
    object_type      => 'TABLE',
    attr1            => 'HR',
    attr2            => 'EMPLOYEES',
    attr3            => NULL,
    attr4            => NULL,
    attr5            => NULL,
    object_id        => obj_id);

  dbms_advisor.set_task_parameter(
    task_name        => name,
    parameter        => 'recommend_all',
    value            => 'TRUE');

  dbms_advisor.execute_task(name);
  end;
end; 
/

Viewing Segment Advisor Results

The Segment Advisor creates several types of results: recommendations, findings, actions, and objects. You can view results in the following ways:

  • With Enterprise Manager

  • By querying the DBA_ADVISOR_* views

  • By calling the DBMS_SPACE.ASA_RECOMMENDATIONS procedure

Table Table 17-4 describes the various result types and their associated DBA_ADVISOR_* views.

Table 17-4 Segment Advisor Result Types

Result Type Associated View Description

Recommendations

DBA_ADVISOR_RECOMMENDATIONS

If a segment would benefit from a segment shrink or reorganization, the Segment Advisor generates a recommendation for the segment. Table 17-5 shows examples of generated findings and recommendations.

Findings

DBA_ADVISOR_FINDINGS

Findings are a report of what the Segment Advisor observed in analyzed segments. Findings include space used and free space statistics for each analyzed segment. Not all findings result in a recommendation. (There may be only a few recommendations, but there could be many findings.) When running the Segment Advisor manually with PL/SQL, if you specify 'TRUE' for recommend_all in the SET_TASK_PARAMETER procedure, then the Segment Advisor generates a finding for each segment that qualifies for analysis, whether or not a recommendation is made for that segment. For row chaining advice, the Automatic Segment Advisor generates findings only, and not recommendations. If the Automatic Segment Advisor has no space reclamation recommendations to make, it does not generate findings.

Actions

DBA_ADVISOR_ACTIONS

Every recommendation is associated with a suggested action to perform: either segment shrink or online redefinition (reorganization). The DBA_ADVISOR_ACTIONS view provides either the SQL that you need to perform a segment shrink, or a suggestion to reorganize the object.

Objects

DBA_ADVISOR_OBJECTS

All findings, recommendations, and actions are associated with an object. If the Segment Advisor analyzes more than one segment, as with a tablespace or partitioned table, then one entry is created in the DBA_ADVISOR_OBJECTS view for each analyzed segment. Table 17-2 defines the columns in this view to query for information on the analyzed segments. You can correlate the objects in this view with the objects in the findings, recommendations, and actions views.


See Also:

Viewing Segment Advisor Results with Enterprise Manager

With Enterprise Manager (EM), you can view Segment Advisor results for both Automatic Segment Advisor runs and manual Segment Advisor runs. You can view the following types of results:

  • All recommendations (multiple automatic and manual Segment Advisor runs)

  • Recommendations from the last Automatic Segment Advisor run

  • Recommendations from a specific run

  • Row chaining findings

You can also view a list of the segments that were analyzed by the last Automatic Segment Advisor run.

To view Segment Advisor results with EM—All runs:

  1. On the database Home page, under the Space Summary heading, click the numeric link next to the title Segment Advisor Recommendations.

    Description of space_summary_seg_advisor.gif follows
    Description of the illustration space_summary_seg_advisor.gif

    The Segment Advisor Recommendations page appears. Recommendations are organized by tablespace.

    Figure 17-3 Segment Advisor Recommendations page

    Description of Figure 17-3 follows
    Description of "Figure 17-3 Segment Advisor Recommendations page"

  2. If any recommendations are present, select a tablespace, and then click Recommendation Details.

    The Recommendation Details page appears. You can initiate the recommended activity from this page (shrink or reorganize).

    Figure 17-4 Recommendation Details page

    Description of Figure 17-4 follows
    Description of "Figure 17-4 Recommendation Details page"

    Tip:

    The list entries are sorted in descending order by reclaimable space. You can click column headings to change the sort order or to change from ascending to descending order.

To view Segment Advisor results with EM—Last Automatic Segment Advisor run:

  1. On the database Home page, under the Space Summary heading, click the numeric link next to the title Segment Advisor Recommendations.

    The Segment Advisor Recommendations page appears. (See Figure 17-3.)

  2. In the View drop-down list, select Recommendations from Last Automatic Run.

  3. If any recommendations are present, click in the Select column to select a tablespace, and then click Recommendation Details.

    The Recommendation Details page appears. (See Figure 17-4.) You can initiate the recommended activity from this page (shrink or reorganize).

To view Segment Advisor results with EM—Specific run:

  1. Start at the Advisor Central page.

    If you ran the Segment Advisor with the Enterprise Manager wizard, the Advisor Central page appears after you submit the Segment Advisor task. Otherwise, to get to this page, on the database Home page, under Related Links, click Advisor Central.

  2. Check that your task appears in the list under the Results heading. If it does not, complete these steps (See Figure 17-2):

    1. In the Search section of the page, under Advisor Tasks, select Segment Advisor in the Advisory Type list.

    2. In the Advisor Runs list, select All or the desired time period.

    3. (Optional) Enter a task name.

    4. Click Go.

      Your Segment Advisor task appears in the Results section.

  3. Check the status of the job. If it is not COMPLETED, click the Refresh button at the top of the page until your task status shows COMPLETED. (Do not use your browser's refresh icon.)

  4. Click the task name.

    The Segment Advisor Task page appears, with recommendations organized by tablespace.

  5. Select a tablespace in the list, and then click Recommendation Details.

    The Recommendation Details page appears. (See Figure 17-4.) You can initiate the recommended activity from this page (shrink or reorganize).

To view row chaining findings

  1. On the database Home page, under the Space Summary heading, click the numeric link next to the title Segment Advisor Recommendations.

    The Segment Advisor Recommendations page appears. (See Figure 17-3.)

  2. Under the Related Links heading, click Chained Row Analysis.

    The Chained Row Analysis page appears, showing all segments that have chained rows, with a chained rows percentage for each.

Viewing Segment Advisor Results by Querying the DBA_ADVISOR_* Views

The headings of Table 17-5 show the columns in the DBA_ADVISOR_* views that contain output from the Segment Advisor. Refer to Oracle Database Reference for a description of these views. The table contents summarize the possible outcomes. In addition, Table 17-2 defines the columns in the DBA_ADVISOR_OBJECTS view that contain information on the analyzed segments.

Before querying the DBA_ADVISOR_* views, you can check that the Segment Advisor task is complete by querying the STATUS column in DBA_ADVISOR_TASKS.

select task_name, status from dba_advisor_tasks
   where owner = 'STEVE' and advisor_name = 'Segment Advisor';
 
TASK_NAME                      STATUS
------------------------------ -----------
Manual Employees               COMPLETED

The following example shows how to query the DBA_ADVISOR_* views to retrieve findings from all Segment Advisor runs submitted by user STEVE:

select af.task_name, ao.attr2 segname, ao.attr3 partition, ao.type, af.message 
  from dba_advisor_findings af, dba_advisor_objects ao
  where ao.task_id = af.task_id
  and ao.object_id = af.object_id
  and ao.owner = 'STEVE';

TASK_NAME          SEGNAME      PARTITION       TYPE             MESSAGE
------------------ ------------ --------------- ---------------- --------------------------
Manual_Employees   EMPLOYEES                    TABLE            The free space in the obje
                                                                 ct is less than 10MB.
 
Manual_Salestable4 SALESTABLE4  SALESTABLE4_P1  TABLE PARTITION  Perform shrink, estimated
                                                                 savings is 74444154 bytes.
 
Manual_Salestable4 SALESTABLE4  SALESTABLE4_P2  TABLE PARTITION  The free space in the obje
                                                                 ct is less than 10MB.

Table 17-5 Segment Advisor Outcomes: Summary

MESSAGE column of DBA_ADVISOR_FINDINGS MORE_INFO column of DBA_ADVISOR_FINDINGS BENEFIT_TYPE column of DBA_ADVISOR_RECOMMENDATIONS ATTR1 column of DBA_ADVISOR_ACTIONS

Insufficient information to make a recommendation.

None

None

None

The free space in the object is less than 10MB.

Allocated Space:xxx: Used Space:xxx: Reclaimable Space :xxx

None

None

The object has some free space but cannot be shrunk because...

Allocated Space:xxx: Used Space:xxx: Reclaimable Space :xxx

None

None

The free space in the object is less than the size of the last extent.

Allocated Space:xxx: Used Space:xxx: Reclaimable Space :xxx

None

None

Perform shrink, estimated savings is xxx bytes.

Allocated Space:xxx: Used Space:xxx: Reclaimable Space :xxx

Perform shrink, estimated savings is xxx bytes.

The command to execute. For example: ALTER object SHRINK SPACE;)

Enable row movement of the table schema.table and perform shrink, estimated savings is xxx bytes.

Allocated Space:xxx: Used Space:xxx: Reclaimable Space :xxx

Enable row movement of the table schema.table and perform shrink, estimated savings is xxx bytes

The command to execute. For example: ALTER object SHRINK SPACE;)

Perform re-org on the object object, estimated savings is xxx bytes.

(Note: This finding is for objects with reclaimable space that are not eligible for online segment shrink.)

Allocated Space:xxx: Used Space:xxx: Reclaimable Space :xxx

Perform re-org on the object object, estimated savings is xxx bytes.

Perform reorg

The object has chained rows that can be removed by re-org.

xx percent chained rows can be removed by re-org.

None

None


Viewing Segment Advisor Results with DBMS_SPACE.ASA_RECOMMENDATIONS

The ASA_RECOMMENDATIONS procedure in the DBMS_SPACE package returns a nested table object that contains findings or recommendations for Automatic Segment Advisor runs and, optionally, manual Segment Advisor runs. Calling this procedure may be easier than working with the DBA_ADVISOR_* views, because the procedure performs all the required joins for you and returns information in an easily consumable format.

The following query returns recommendations by the most recent run of the Auto Segment Advisor, with the suggested command to run to follow the recommendations:

select tablespace_name, segment_name, segment_type, partition_name,
recommendations, c1 from
table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'));


TABLESPACE_NAME                SEGMENT_NAME                   SEGMENT_TYPE
------------------------------ ------------------------------ --------------
PARTITION_NAME
------------------------------
RECOMMENDATIONS
-----------------------------------------------------------------------------
C1
-----------------------------------------------------------------------------
TVMDS_ASSM                     ORDERS1                        TABLE PARTITION
ORDERS1_P2
Perform shrink, estimated savings is 57666422 bytes.
alter table "STEVE"."ORDERS1" modify partition "ORDERS1_P2" shrink space
 
TVMDS_ASSM                     ORDERS1                        TABLE PARTITION
ORDERS1_P1
Perform shrink, estimated savings is 45083514 bytes.
alter table "STEVE"."ORDERS1" modify partition "ORDERS1_P1" shrink space
 
TVMDS_ASSM_NEW                 ORDERS_NEW                     TABLE
 
Perform shrink, estimated savings is 155398992 bytes.
alter table "STEVE"."ORDERS_NEW" shrink space
 
TVMDS_ASSM_NEW                 ORDERS_NEW_INDEX               INDEX
 
Perform shrink, estimated savings is 102759445 bytes.
alter index "STEVE"."ORDERS_NEW_INDEX" shrink space

See Oracle Database PL/SQL Packages and Types Reference for details on DBMS_SPACE.ASA_RECOMMENDATIONS.

Configuring the Automatic Segment Advisor

The Automatic Segment Advisor is an automated maintenance task. As such, you can use Enterprise Manager or PL/SQL package procedure calls to modify when (and if) this task runs. You can also control the resources allotted to it by modifying the appropriate resource plans.

You can call PL/SQL package procedures to make these changes, but the easier way to is to use Enterprise Manager.

To configure the Automatic Segment Advisor task with Enterprise Manager:

  1. Log in to Enterprise Manager as user SYSTEM.

  2. On the Database Home page, under the Space Summary heading, click the numeric link next to the label Segment Advisor Recommendations.

    Description of space_summary_seg_advisor.gif follows
    Description of the illustration space_summary_seg_advisor.gif

    The Segment Advisor Recommendations page appears.

  3. Under the Related Links heading, click the link entitled Automated Maintenance Tasks.

    The Automated Maintenance Tasks page appears.

  4. Click Configure.

    The Automated Maintenance Tasks Configuration page appears.

    Description of edit_job_seg_advisor.gif follows
    Description of the illustration edit_job_seg_advisor.gif

  5. To completely disable the Automatic Segment Advisor, under Task Settings, select Disabled next to the Segment Advisor label, and then click Apply.

  6. To disable the Automatic Segment Advisor for specific maintenance windows, clear the desired check boxes under the Segment Advisor column, and then click Apply.

  7. To modify the start and end times and durations of maintenance windows, click Edit Window Group.

    The Edit Window Group page appears. Click the name of a maintenance window, and then click Edit to change the window's schedule.

Viewing Automatic Segment Advisor Information

The following views display information specific to the Automatic Segment Advisor. For details, see Oracle Database Reference.

View Description
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.
DBA_AUTO_SEGADV_CTL Contains control information that the Automatic Segment Advisor uses to select and process segments. Each row contains information on a single object (tablespace or segment), including whether the object has been processed, and if so, the task ID under which it was processed and the reason for selecting it.

Shrinking Database Segments Online

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:

  • Compaction of data leads to better cache utilization, which in turn leads to better online transaction processing (OLTP) performance.

  • The compacted data requires fewer blocks to be scanned in full table scans, which in turns leads to better decision support system (DSS) performance.

Segment shrink is an online, in-place operation. DML operations and queries can be issued during the data movement phase of segment shrink. Concurrent DML operation are blocked for a short time at the end of the shrink operation, when the space is deallocated. Indexes are maintained during the shrink operation and remain usable after the operation is complete. Segment shrink does not require extra disk space to be allocated.

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.

Segment shrink requires that rows be moved to new locations. Therefore, you must first enable row movement in the object you want to shrink and disable any rowid-based triggers defined on the object. You enable row movement in a table with the ALTER TABLE ... ENABLE ROW MOVEMENT command.

Shrink operations can be performed only on segments in locally managed tablespaces with automatic segment space management (ASSM). Within an ASSM tablespace, all segment types are eligible for online segment shrink except these:

  • IOT mapping tables

  • Tables with rowid based materialized views

  • Tables with function-based indexes

  • SECUREFILE LOBs

See Also:

Oracle Database SQL Language Reference for more information on the ALTER TABLE command.

Invoking Online Segment Shrink

Before invoking online segment shrink, view the findings and recommendations of the Segment Advisor. For more information, see "Using the Segment Advisor".

You invoke online segment shrink with Enterprise Manager (EM) or with SQL commands in SQL*Plus. The remainder of this section discusses the command line method.

Note:

You can invoke segment shrink directly from the Recommendation Details page in EM. (See Figure 17-4.) Or, to invoke segment shrink for an individual table in EM, display the table on the Tables page, select the table, and then click Shrink Segment in the Actions list. (See Figure 17-1.) Perform a similar operation in EM to shrink indexes, materialized views, and so on.

You can shrink space in a table, index-organized table, index, partition, subpartition, materialized view, or materialized view log. You do this using ALTER TABLE, ALTER INDEX, ALTER MATERIALIZED VIEW, or ALTER MATERIALIZED VIEW LOG statement with the SHRINK SPACE clause. Refer to Oracle Database SQL Language Reference for the syntax and additional information on shrinking a database object, including restrictions.

Two optional clauses let you control how the shrink operation proceeds:

  • The COMPACT clause lets you divide the shrink segment operation into two phases. When you specify COMPACT, Oracle Database defragments the segment space and compacts the table rows but postpones the resetting of the high water mark and the deallocation of the space until a future time. This option is useful if you have long-running queries that might span the operation and attempt to read from blocks that have been reclaimed. The defragmentation and compaction results are saved to disk, so the data movement does not have to be redone during the second phase. You can reissue the SHRINK SPACE clause without the COMPACT clause during off-peak hours to complete the second phase.

  • The CASCADE clause extends the segment shrink operation to all dependent segments of the object. For example, if you specify CASCADE when shrinking a table segment, all indexes of the table will also be shrunk. (You need not specify CASCADE to shrink the partitions of a partitioned table.) To see a list of dependent segments of a given object, you can run the OBJECT_DEPENDENT_SEGMENTS procedure of the DBMS_SPACE package.

As with other DDL operations, segment shrink causes subsequent SQL statements to be reparsed because of invalidation of cursors unless you specify the COMPACT clause.

Examples

Shrink a table and all of its dependent segments (including BASICFILE LOB segments):

ALTER TABLE employees SHRINK SPACE CASCADE;

Shrink a BASICFILE LOB segment only:

ALTER TABLE employees MODIFY LOB (perf_review) (SHRINK SPACE);

Shrink a single partition of a partitioned table:

ALTER TABLE customers MODIFY PARTITION cust_P1 SHRINK SPACE;

Shrink an IOT index segment and the overflow segment:

ALTER TABLE cities SHRINK SPACE CASCADE;

Shrink an IOT overflow segment only:

ALTER TABLE cities OVERFLOW SHRINK SPACE;

See Also:

Oracle Database SecureFiles and Large Objects Developer's Guide for more information about LOB segments

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.

Prior to deallocation, you can run the UNUSED_SPACE procedure of the DBMS_SPACE package, which returns information about the position of the high water mark and the amount of unused space in a segment. For segments in locally managed tablespaces with automatic segment space management, use the SPACE_USAGE procedure for more accurate information on unused space.

See Also:

Oracle Database PL/SQL Packages and Types Reference contains the description of the DBMS_SPACE package

The following statements deallocate unused space in a segment (table, index or cluster):

ALTER TABLE table DEALLOCATE UNUSED KEEP integer;
ALTER INDEX index DEALLOCATE UNUSED KEEP integer;
ALTER CLUSTER cluster DEALLOCATE UNUSED KEEP integer;

The KEEP clause is optional and lets you specify the amount of space retained in the segment. You can verify that the deallocated space is freed by examining the DBA_FREE_SPACE view.

See Also: