Space Manager with LiveReorg 9.1 - User Guide

View Recycle Bin and Free Space

The Recycle Bin is a logical container that is used for dropped tables and their dependent objects. Oracle automatically preserves dropped objects in the Recycle Bin in case you need to recover them. Oracle then treats the space used by these objects as free space. The space can be reclaimed for use by other objects.

In the Explorer, you can view Recycle Bin space and objects for each database and for each tablespace. You can also view information about free space for each tablespace.

To view Recycle Bin space for a database or tablespace

1. In the Explorer tree-list, expand the node for the database or tablespace you are interested in.

2. Click on the Recycle Bin icon under the database or tablespace node. The list of segments in the Recycle Bin displays on the right side of the Explorer window.
3. Check the Allocation column to see how much Recycle Bin space can be reclaimed for use by other objects.

Tip: Click on a database or tablespace name in the Explorer tree-list to display a pie chart which shows the amount of Recycled space as a percentage of total space.

To view free space for a tablespace

1. In the Explorer tree-list, expand a tablespace node.
2. Click on the Free Space icon under the tablespace node. The list of free extents displays on the right side of the Explorer window.

Tip: Click on a database or tablespace name in the Explorer tree-list to display a pie chart which shows the amount of free space as a percentage of total space.

To purge an object from the Recycle Bin

1. Select the Recycle Bin node for a database or tablespace.
2. In the Segments pane, right-click a segment (object) and select Purge or Purge object.

To restore a table from the Recycle Bin

1. Select the Recycle Bin node for a database or tablespace.
2. In the Segments pane, right-click a table or table partition and select Restore table.

To purge the Recycle Bin

Do one of the following:

• Right-click a Recycle Bin node in the Explorer tree-list and select Purge Recycle Bin.
• Select the Recycle Bin node for a database or tablespace. In the Segments pane, click Purge Recycle Bin.

View Reorg Need

View Reorg Need from the Explorer

Use the Reorg Need column in the Segments/Objects grid and the Datafile Map to identify objects in need of reorganization.

Reorg need is a measure of the benefit of reorganizing an object to resolve space-use problems. Benefit is the improvement in performance to be gained and wasted space to be recovered. The higher the value, the greater the benefit.

Note: Space Manager provides the best Reorg Need and Wasted space information when statistics are current in the Repository. Be sure to run the Repository Update job on regular or nightly basis (see Change Repository Update Schedule).

Use the following Explorer features to identify objects with the greatest need for reorganization.

To learn more about the reorg need value, review the following topics:

• How Reorg Need Is Calculated
• Reorg Need Factors
• Reorg Need and Statistics Collection Methods
• Reorg Need After Reorganization

Explorer - Segments Grid

Each segment/object grid in the Explorer includes a Reorg Need column. This column provides the calculated reorg need value for each object in the grid.

To view Reorg Need column

1. In the Explorer, select a tablespace or owner to view reorg need.
   • Tablespace: Select a tablespace in the Explorer tree-list (left pane). The grid on the right lists the tablespace segments and includes a Reorg Need column.
   • Owner: Select an owner in the Explorer tree-list (left pane). Expand the owner node and then select Tables or Indexes. The grid on the right lists the owner's objects and includes a Reorg Need column.

2. Review the Reorg Need column to determine those segments that would benefit from reorganization. Click the column header twice to sort from highest to lowest need.

   See Monitor Tablespace and Object Storage for more information.

Explorer - Advanced Search

Use the Advanced Search window to search for objects based on their reorg need value. See Advanced Search for more information.

Explorer - Datafile Map

Use the Datafile Map to view segments with extents that are blocking you from resizing the datafile.

To view segments with blocking extents

1. Select the tablespace for which you want to view a datafile map.
2. Expand the tablespace datafile node and select a datafile. The datafile map displays along with a list of segments with blocking extents.
3. Use the list of segments with blocking extents to determine which segments you want to reorganize.

4. Right-click a segment in the list to launch the Reorg Manager.

   See Monitor Datafile Storage for more information.

How Reorg Need Ratio Is Calculated

Reorg need is calculated based on factors that have a negative impact on database performance. These factors include wasted blocks and excessive chaining (which force Oracle to perform additional reads).

Which factors are used for an object depends on its type and whether it resides in a tablespace that is data dictionary managed or locally managed. The following factors are used for different object types:

• Wasted data blocks—Used for all objects.
• Chained rows—Used for tables only.

Reorg Need Calculations

To determine reorg need for an object, Space Manager performs the following calculations:

1. Each reorg need factor is given a normalized value between zero and one. For example, if a table has no chained rows, the Chained Rows factor is zero. If all of a table’s rows are chained, the Chained Rows factor is one. Factors that are not used in calculating reorg need for an object are given a value of zero.

2. Each reorg need factor is multiplied by a weight. Default weights are 70% for wasted blocks and 30% for chained rows. Default weights add up to 100%.

   Note: You can specify the default weight values in Tools | Options | Reorg Need. See Reorg Need Options.

3. All weighted values are added together to arrive at a reorg need value in the range of 1 to 100.

   Note: Statistics for reorg need factors are taken from the Oracle data dictionary and DBMS_SPACE.

Related Topics

Reorg Need Factors

Reorg Need and Statistics Collection Methods

Reorg Need After Reorganization