Viewing Segment Advisor Recommendations and Reclaiming Space

Each Segment Advisor recommendation includes a button that you can click to begin the space reclamation process. Although space reclamation is an online process, it can consume significant resources, so you should reclaim space during off-peak hours.

To view Segment Advisor recommendations and reclaim space:

1. Go to the Database Home page.

   See "Accessing the Database Home Page".

2. In the Space Summary section, click the numeric link adjacent to Segment Advisor Recommendations.

   The Segment Advisor Recommendations page appears, showing recommendations from the most recent automatic and manual runs of the Segment Advisor.

   Segment Advisor Recommendations Page

   
   Description of "Segment Advisor Recommendations Page"
   
   

3. (Optional) In the View list, select Recommendations for Last Automatic Run to view recommendations from only the most recent automatic run of the Segment Advisor.

4. Select a tablespace, and then click Recommendation Details.

   The Recommendation Details for Tablespace page appears.

   This page lists the tablespace segments for which online segment shrink or reorganization is recommended.

   Recommendation Details for Tablespace Page

   
   Description of "Recommendation Details for Tablespace Page"
   
   

5. (Optional) Reduce the number of segments displayed in the segment list by entering search criteria in the Schema, Segment, Partition, or Minimum Reclaimable Space fields, and then clicking Search.

6. Examine the recommendation for each segment, and then proceed as follows:

   • If the recommendation for any segments is to reorganize, then start reorganization for those segments by clicking the Reorganize button, or select multiple segments and then click the Reorganize button above the table. Follow the directions in the succeeding pages to generate a reorganization script for the selected segments.

   • If the recommendation for any segments is to shrink, then proceed with Step 7 through Step 13 for those segments.

7. Do one of the following to select one or more segments to shrink:

   • To reclaim space in a single segment, click Shrink under the Recommendation column for that segment.

   • To shrink one or more segments, select the segments, and then click the Shrink button above the table.

8. On the Shrink Segment: Options page, accept Compact Segments and Release Space.

   This option returns freed space to the tablespace. If you do not want to release the freed space to the tablespace, then choose Compact Segments. You can rerun the shrink process later to release the freed space.

   Note: Choose Compact Segments if you believe that you may have long-running queries currently in operation that involve the segments being shrunk. Long-running queries that were started before the shrink operation completes might attempt to read from blocks that have been reclaimed, which results in an error. Alternatively, you can schedule the shrink operation for an off-peak period in Step 10.

9. Click Implement.

   The Shrink Segment: Schedule page appears.

10. Note your job name, or replace it with a job name of your choosing.

11. Under the heading Start, select Immediately.

    Because the shrink operation can be resource intensive, you can also select Later and schedule the operation for an off-peak period.

12. Click Submit.

    The Scheduler Jobs page appears, and shows the shrink job in the Running subpage.

13. Click Refresh to update the page until the job disappears from the Running subpage.

    You can then switch to the History or All subpage to view the completed status of the job.