Monitor Database Storage
You can use the Explorer to monitor database storage. Two views are available to display database storage information for each database.
Database view—In the Explorer, select a database in the left pane to display database information in the right pane. A summary of the database properties and statistics displays, as well as a pie chart showing total database space usage.
Note: Clicking a database name in the left pane also opens the connection.
- Tablespaces view—In the Explorer, click the Tablespaces node under a database to display a list of tablespaces. A tablespaces grid displays in the right pane listing the tablespaces included in the database, as well as the total sizes and counts for various segment types for each tablespace.
To view a summary of database properties and statistics
Select a database in the Explorer tree-list. The Properties, Statistics, and Graph panels display.
- Properties—Lists the database properties.
- Statistics—Lists database total size and free space, and total counts and total sizes for tables, indexes, LOBs.
- Graph—Displays a pie chart that lets you visualize space-use percentages by segment type for the entire database.
Note: Status information for the Repository and for the Quest Server Agent is also displayed above the Properties panel.
To view the Tablespaces list and summary information
Select the Tablespaces node in the Explorer tree-list. A summary of tablespace information, as well as a list of tablespaces, displays in the right pane.
- Summary—Lists total number of tablespaces, grouped by tablespace category.
Tablespaces—Displays a list of tablespaces and a grid which displays storage information for each tablespace in the database. The grid lists the total size and total count for tables, indexes, and LOBs for each tablespace.
Allocation Chart—The Allocation Chart column is a visual presentation of the amount of allocated and free space for each tablespace relative to the largest tablespace. Use the charts to easily compare tablespace sizes. Also use the charts to compare free to allocated space for each tablespace.
Note: For Oracle 11 (or later) databases, recycle bin space allocation information is included at the database and tablespace level.
Tip: You can search for objects/segments by name using the Search text box in the upper-right corner of the Explorer window.
When you select a database in the Explorer tree-list, Space Manager displays a list of the top issues for that database. The Issues list displays in a pane below the database summary information.
For each issue, the list provides an icon for easy identification of issue severity, the object type, and a description of the issue.
You can use the Issues list to identify objects in need of reorganization and launch the Reorg Manager directly from this list.
To view the Top Issues for a database
- Select a database in the Explorer tree-list. The Issues list displays in the right-pane (below the summary information).
Review the list of top issues. Click a column heading to sort by the column. Icons identify the most urgent issues. Review the following icon definitions:
|Severe issue. Indicates a severe issue, such as a tablespace that is very low on free space.
|Warning. Indicates an issue that might need attention, such as a tablespace that is moderately low on free space or a table that has wasted space.
|Free space. Indicates the tablespace has a large amount of free space. Consider releasing this free space back to the file system.
To launch the Reorg Manager from the Top Issues list
- Right-click an object in the list and select Reorg Manager.
Monitor Tablespace and Object Storage
In the Explorer, from the database level, you can drill down to storage information for individual tablespaces and their segments or objects. To select a tablespace, click the tablespace name in the Explorer tree-list.
Note: Read-only tablespaces are displayed with the international “no” symbol. Offline tablespaces have a red center.
To view a summary of tablespace information
Select a tablespace in the Explorer tree-list. The Properties, Statistics, and Graph panels display above the Segments grid.
- Properties—Lists the tablespace properties.
- Statistics—Lists tablespace total size and free space, and total counts and total sizes for tables, indexes, LOBs.
- Graph—Use the graph to quickly visualize how space is allocated and how much is free.
Note: For Oracle 11 (or later) databases, recycle bin space allocation information is included in the tablespace summary.
To list tablespace segments
Select a tablespace in the Explorer tree-list. The Segments list displays below the Properties, Statistics, and Graph panels.
- The Segments list displays the following information for each segment in the tablespace.
- Segment owner
- Segment name
- Partition name
- Segment type
- Wasted space and space allocated
- Reorg need
Note: Objects that do not use storage are not displayed in the Segments grid. Instead, their components that do use storage are displayed. For example, partitions and subpartitions are displayed instead of their parent partitioned object. IOT indexes are displayed instead of IOTs.
To view Reorg Need
The Reorg Need column provides the reorg need value calculated by Space Manager for each object in a grid. See View Reorg Need from the Explorer for more information.
- Click the Reorg Need column header to sort objects by reorg need.
- Select Reorg Need in the Group By field to group objects by reorg need.
- To modify Reorg Need default values, go to Tools | Options | Reorg Need. See Reorg Need Options for more information.
To view objects by Owner
- In the Explorer tree-list, for a selected database, expand the Owners node.
- Select an owner and expand the node.
Click Tables or Indexes to display the objects owned by that user.
Tip: To sort objects by reorg need, click the Reorg Need column header in the object grid.
To use the Search field
You can search for an object, segment, or partition by name. Enter a partial or whole word to find a match. The field uses incremental search. All names that include the string entered are returned.
Space Manager also provides an advanced search feature. See Advanced Search.
- In the Explorer tree-list, select a database, a tablespace, or an owner to narrow your search to that group of objects.
- In the Search text box in the upper-right corner of the Explorer window, enter a whole or partial name.
- Space Manager uses incremental search to find matching segment, object, and partition names. Non-whole-word matching is used to return names that contain a match in any segment of the name.
To use the Group By field
- In the Explorer tree-list, select a tablespace (or select an owner and then tables/indexes).
- In the Group by field, select the column to group by.
- After grouping objects/segments, view the total allocation for each group in the Allocation column.
- Review these tips:
- If grouping by Reorg Need, you can modify the threshold values for High, Medium, and Low Reorg Need in Tools | Options | Reorg Need. See Reorg Need Options.
- Click a column header to sort by that field before grouping. After grouping, the sort order is preserved within each group.
To launch the Reorg Manager or Partitioning Wizard
- Select one or more objects in a segments or objects grid.
- Then right-click and select Reorg Manager or Partitioning Wizard.
Use Advanced Search
The Space Manager Advanced Search feature allows you to use multiple search criteria to find objects. This is useful when you want to find a diverse set of objects to include in one reorganization script group. Search for objects within a database or a tablespace. Access the Advanced Search feature from the Explorer window.
To use Advanced Search
- In the Explorer tree-list, select a database, tablespace, or owner to search. You can also select the Tables or Indexes node under an owner.
- Click the Advanced button beside the Search box in the Explorer window to open the Advanced Search window.
- Use the fields provided to create one or more conditions to use as your search criteria. Select an operator and then select or enter a value in each applicable field.
- After specifying search criteria, click Start New Search. The search results are displayed in the lower pane.
- Click the Show search text button to display the text of your search condition/conditions.
- To refine your search, you can modify criteria or add additional criteria and click Start New Search.
- To add to your current search results, you can add one or more new conditions (or change values in existing conditions) and then click Add to Results or click and select Add to results. The new or modified conditions are used to select objects which are added to the existing search results.
- To remove objects from the current results, you can add one or more new conditions (or change values in existing conditions) and then click and select Remove from results. The objects specified by the new or modified conditions are removed from the results.
- To search within the current results, add or modify conditions and then click and select Search within results.
- Review the following additional instructions:
- When using the in or not in operators, enter search values as a comma-separated list.
- To perform a case-sensitive search, surround the value in double quotes. Unquoted text values are converted to uppercase before performing a search.
- Empty fields are not included in the search criteria.
- In the Group by field, select a column to group results by column values.
To launch the Reorg Manager or Partitioning Wizard from search results
- Select one or more objects in the search results grid.
- Then right-click the selection and select Reorg Manager or Partitioning Wizard.
Monitor Datafile and Extent Storage
In the Explorer, you can drill down from the tablespace level to view storage information for each datafile. The datafile map helps you to visualize space usage for a datafile.
To display datafile storage and datafile map
- Select a tablespace from the Explorer tree-list.
- Expand the tablespace node. Then expand the Datafiles node to display a list of datafiles.
Select a datafile from the list. The following information is displayed:
- Summary information—Size, free space, maximum size (a size is displayed only if the datafile is auto-extensible), and reclaimable space.
- Segments with blocking extents—A list of segments with segment type, name, and owner.
- Datafile map—A color-coded graphic representation of datafile space use. Segments/extents are color-coded to easily view space usage by segment type. Hover over each color-coded segment/extent to display the segment name. Click on an extent to highlight all extents in the segment.
Note: Datafiles are listed by their Oracle-assigned datafile number.
- Use the datafile map to view storage information:
- Hover over each cell to display the names of all segments in that cell.
- Click on a cell in the map to highlight all extents allocated to that segment.
- Toggle between two views: Segment Type and Reorg Need.
- Segment Type—Select this view to color-code segments by segment type. Use the legend to identify segment type.
Reorg Need—Select this view to color-code segments by reorg need. Use the legend to identify reorg need severity.
Note: To modify threshold settings for reorg need severity, select Tools | Options | Reorg Need.
- Identify segments with blocking extents.
- The Segments with blocking extents list contains extents that are blocking you from resizing the datafile.
- Click on a segment name in the list to highlight all extents within that segment in the datafile map.
Note: Press F5 to refresh the datafile map.
To launch the Reorg Manager from Segments list
- In the Segments with blocking extents list, right-click a segment and select Reorg Manager.
To view a list of datafiles for a tablespace
- In the Explorer tree-list, select the Datafiles node for a tablespace. A list of datafiles for that tablespace displays.
- Review the list to see storage information details for each datafile, including size, how much space is allocated and how much is free space, and whether the datafile is auto-extensible.
Tip: You can add a datafile using the Tablespace Properties window (right-click a tablespace name in the tree-list and select Tablespace Properties).