Oracle Enterprise Manager Performance Monitoring User's Guide

Using Oracle Tablespace Manager

Starting Oracle Tablespace Manager
Oracle Tablespace Manager Main Window
Obtaining an Overview of Tablespace Storage
Obtaining an Overview of Datafiles in a Tablespace
Monitoring Segments of a Tablespace
Managing Storage in a Tablespace

This chapter covers how you use Oracle Tablespace Manager, including the following topics:

Starting Oracle Tablespace Manager
Oracle Tablespace Manager Main Window
Obtaining an Overview of Tablespace Storage in a Database
Obtaining an Overview of Datafiles for a Tablespace
Monitoring Segments in a Tablespace
Managing Storage in a Tablespace

Starting Oracle Tablespace Manager

You start Oracle Tablespace Manager as you would start any typical application integrated with Oracle Enterprise Manager. See Starting Performance Monitoring Applications on page 1-3 for a description of how to do so.

Oracle Tablespace Manager Main Window

After you start Oracle Tablespace Manager, the main window is displayed. Figure 10-1 shows the Oracle Tablespace Manager main window.

Figure 10-1: Oracle Tablespace Manager Main Window

The Oracle Tablespace Manager main window includes the following components:

Title bar
Toolbar
Status bar
Menu bar
Main window

Oracle Tablespace Manager Titlebar

The title bar of the Oracle Tablespace Manager main window displays the name of the application and the name of the database instance to which a connection has been made.

Oracle Tablespace Manager Toolbar

The Oracle Tablespace Manager toolbar includes icons that enable you to perform the following menu commands: Change Database Connection, Refresh, Filter Folder, Help Contents, and Help.

Oracle Tablespace Manager Status Bar

The Oracle Tablespace Manager status bar displays information about the current operation on the left.

Oracle Tablespace Manager Main Window

As Figure 10-1 shows, the Oracle Tablespace Manager main window includes a tree list in the left pane of the window. The Oracle Tablespace Manager tree list operates like the Oracle Enterprise Manager tree list, except that it only provides information about the tablespaces of the database instance.

When you start Oracle Tablespace Manager and the main window appears, the top container in the navigator tree shows the database instance being monitored. The Tablespaces container containing the tablespaces of that instance is also displayed. From the Tablespaces container you can drill down as follows:

Single-clicking on the Tablespaces container displays a multi-column list showing space usage information for each tablespace in the instance.
Double-clicking on the Tablespaces container shows the next level of detail in the tree list, displaying containers representing each individual tablespace in the instance.

For more information on how a navigator tree is populated, see "Navigator," in the Oracle Enterprise Manager Administrator's Guide.

Oracle Tablespace Manager Menu Bar

The Oracle Tablespace Manager menu bar includes the following menus:

File
View
Tablespace
Help

File Menu

The File menu items allow you to change the database connection and exit the Oracle Tablespace Manager application.

The File menu includes the following menu items:

Change Database Connection

Allows you to connect to another database instance.

Enable Roles

Displays the Enable Roles dialog box, from which you can select roles to enable. For more information, see "Overview of Database Tools," in the Oracle Enterprise Manager Administrator's Guide.

Exit

Exits the Oracle Tablespace Manager application.

View Menu

The View menu items allow you to show (default) or hide the toolbar and status bar. For more information, see "Overview of Database Tools," in the Oracle Enterprise Manager Administrator's Guide.

The View menu includes the following menu items:

Refresh

Refreshes the data displayed by Oracle Tablespace Manager.

Filter Folder

Allows you to specify filters for displaying tree list object.

Expand One Level

Expands the selected container in the Oracle Tablespace Manager navigator tree by one level of detail.

Collapse Branch

Hides the level(s) of detail below the selected container in the Oracle Tablespace Manager navigator tree.

Collapse All

Hides all levels of detail below the database container in the Oracle Tablespace Manager navigator tree.

Toolbar

Shows/hides the toolbar.

Status Bar

Shows/hides the status bar.

Tablespace Menu

The Tablespace menu items allow you to coalesce adjacent free extents in the database.

The Tablespace menu includes the following menu items:

Coalesce

Joins adjacent free extents in the database.

Help Menu

The Help menu includes the following menu items:

Contents

Displays an overview of the Oracle Performance Monitoring applications.

Search for Help On

Displays an alphabetical list of Help topics.

Using Help

Displays information about using the Help system.

About Oracle Tablespace Manager

Displays version information for this release of Oracle Tablespace Manager.

Obtaining an Overview of Tablespace Storage

To obtain an overview of the tablespaces in the database instance, single-click on the Tablespaces container in the left pane. The right pane of the main window displays a multi-column list displaying storage information for each tablespace of the database instance. This list includes the following fields:

Name

Tablespace name.

# Datafiles

Number of datafiles in the tablespace.

Total Blocks

Total number of blocks in the tablespace.

Free Blocks

Number of free blocks in the tablespace.

% Free

Percentage of total number of blocks in the tablespace that are free blocks. A horizontal bar in the background of this field graphically represents the percentage of free blocks in the tablespace.

# Adj. Free Extents

Number of adjacent free extents in the tablespace.

Obtaining an Overview of Datafiles in a Tablespace

To obtain an overview of the datafiles in a given tablespace, from the Oracle Tablespace Manager tree list, single-click on the Datafiles container of the tablespace of interest to you. The right pane of the main window displays a multi-column list including information for each datafile of this particular tablespace. This table includes a subset of the fields described in the previous section, as follows:

Name

Datafile name, including its directory path.

Total Blocks

Total number of blocks in the datafile.

Free Blocks

Number of free blocks in the datafile.

% Free

Percentage of total number of blocks in the datafile that are free blocks. A horizontal bar in the background of this field graphically represents the percentage of free blocks in the datafile.

Monitoring Segments of a Tablespace

To monitor the segments of a given tablespace, in the tree list, single-click on an individual Tablespace container. Figure 10-2 shows an example of the Segments page that displays.

Figure 10-2: Example of the Oracle Tablespace Manager Segments Page

The Segments page includes the following elements:

Left pane

Lists the segments of the tablespace (or datafile) selected in the tree list. When the Segments page first displays, the first segment in the list is automatically selected. If no segments have been allocated for the selected tablespace, the left pane is blank.

Click on a segment listed in this pane to graphically highlight how the extents in the selected tablespace (or datafile) are allocated for this segment.

Right pane

Displays the space allocation graphic for the selected tablespace (or datafile), showing how space for its segments and extents have been allocated.

Colored space denotes used space; white space denotes free space. The allocated extents of the segment that is selected in the left pane are highlighted in yellow. The yellow extent with the highest block ID is outlined by a broken white line to indicate that it is the selected extent. The allocated extents of other (nonselected) segments in the tablespace (or datafile) are cyan (light blue). Header blocks appear in grey or white with blue horizontal lines.

Black vertical lines separate extents in the space allocation graphic. If you see large black-colored sections, it is because there are so many extents in the segment that the colors representing the extents themselves are not visible. To minimize this problem, maximize the size of the Oracle Tablespace Manager main window.

There are two ways to display the extents for a selected segment. The first way is to select the segment in the Left pane; the extents in the Right pane that comprise the selected segment then change color. The second way is to click on an extent in the Right pane. All of the extents comprising the segment associated with the extent you just clicked then are highlighted in yellow, and the associated segment is then automatically selected in the Left pane. In this case, a broken white line outlines the extent you selected in the Right pane.

Sort By

Click on a sorting criterion in this combo box to organize the list of segments in the left pane. Options include:

Number of Extents: Segments are sorted first by the number of extents in the segment then alphabetically by segment name (default).

Segment Name: Segments are sorted alphabetically by segment name.

Segment Owner: Segments are sorted alphabetically by segment owner then by segment name.

Segment Type: Segments are sorted alphabetically by segment type then by segment name.

Segment Information

Blocks: Total number of blocks in the selected segment.

Owner: Owner of the selected segment.

Type: Type of the selected segment. Options include: CLUSTER, INDEX, ROLLBACK, and TABLE.

Extents: Number of extents in the selected segment.

1 line =

Number of blocks in a single line of the space allocation graphic. Because Oracle Tablespace Manager attempts to display all of the extents of a selected tablespace (or datafile), this value can vary if you resize the Oracle Tablespace Manager main window.

Extent Information

Blocks: Number of blocks in the selected extent.

Block ID: ID of the first block in the selected extent.

Extent ID: ID of the selected extent.

Defragment

Displays the Defragmentation property sheet. The Defragment button is enabled only when a segment of the Table type has been selected from the left-hand pane of the Segments page. For more information, Managing Storage in a Tablespace on page 10-9.

Help

Displays Help for the Segments page.

Managing Storage in a Tablespace

This section covers the following topics:

Defragmenting a table segment
Joining adjacent free extents in the database

Defragmenting a Table Segment in a Tablespace

To defragment a table segment, perform the following steps:

1.: Make sure Oracle Enterprise Manager is running.
: Because Oracle Tablespace Manager uses the Oracle Enterprise Manager job subsystem to perform table segment defragmentation, Oracle Enterprise Manager Console must also be running at this time.
2.: In the Oracle Tablespace Manager tree list, click on the tablespace or datafile of interest.
3.: When the Segments page displays, select the table segment which you want to defragment.
: Only a segment of Type TABLE can be defragmented; the Defragment button is disabled for selected segments of any other type.

Attention:

If you fragment a table which is part of a cluster, the table is removed from the cluster.

4.: On the Segments page, click the Defragment button.
5.: On the resulting Defragmentation dialog box, specify either default or advanced defragmentation, as follows:

: Continue: Click on the Continue button to display a window allowing you to note the filename of the job, and then confirm immediate submission of the job to defragment the selected table segment using the default options. These default options include compressing the defragmented extents into a single extent.
: Advanced: Click on the Advanced button to display the Defragmentation property sheet, as described in Specifying Advanced Defragmentation Options on page 10-10.

6.: After you note the job filename and then confirm immediate submission of a job to defragment the table segment (either from the Defragmentation dialog box or the Advanced Defragmentation property sheet), the default version of the Oracle Tablespace Manager main window displays.
7.: Check the Console job subsystem to make sure the defragmentation job has completed execution.
8.: To see the results of the defragmentation, choose Refresh from the View menu, reselect the tablespace (or datafile) of interest, display the Segments page, and then select the defragmented segment from the segments list.

Specifying Advanced Defragmentation Options

To defragment a table segment using storage parameters other than the default parameters, click on the Advanced button of the Defragmentation dialog box.

The Defragmentation property sheet contains the following elements:

Server Export Directory

You can enter the directory path on the server in which the export file and temporary files are to be written. This directory path cannot contain any environment variables.

Attention:

If you do not specify a server export directory path, these files will be written to the directory in which the defragmentation job script is run. In either case, the Oracle user must have permissions to write to the directory for these files, or the defragmentation job will fail.

Storage Options

Retain Default: Click on this button if you want to defragment the selected table segment using the current storage parameters, as shown in the fields of this property sheet.

Modify: Click on this button in the Storage Options group if you want to defragment the selected table segment using modified storage parameters. Clicking on this button allows you to modify many of the other fields of this property sheet.

Compress: Click on this button (default) if you want to defragment the selected table by compressing it into a single extent.

Tablespace

Name of the tablespace containing the segment you want to defragment.

Extents

Initial Size: Size of the first extent of the table segment.

Units: Size of the measurement for initial size of the table segment: Bytes, Kilobytes, or Megabytes. This value cannot be edited.

Next Size: Size of the next extent to be allocated to the table segment. The default value is the size of five data blocks. The smallest permissible value is the size of one data block.

Bytes: Size of the measurement for the next size of the table segment: Bytes, Kilobytes, or Megabytes.

Increase Size By: Percent by which each extent grows (after the second extent) compared to the previous extent.

Minimum Number: Total number of extents allocated when a segment is created. The default value is 1. You can enter a value of 1 or greater.

Maximum Number: Total number of extents, including the first, that Oracle can allocate for the table segment. You can enter a value of 1 or greater. The default value varies, depending on the database block size.

Space Usage

% Free: Percentage of space in each of the data blocks of the table segment that is reserved for future updates to the table segment. You can enter a value from 0 to 99.

% Used: Minimum percentage of used space that an Oracle database maintains for each data block of the table segment. A block becomes a candidate for row insertions when its used space falls below the % Used value. You can enter a value from 1 to 99. The default value is 40.

Number of Transactions

Initial: Initial number of transaction entries allocated within each data block allocated to the table segment. You can enter a value from 1 or 2 (for clusters and indexes to 255).

Maximum: Maximum number of concurrent transactions that can update a data block allocated to the table segment. You can enter a value from 1 to 255.

Free Lists

Lists: Number of free lists for each of the free list groups for the table, cluster, or index. You can enter a value of 1 or more. The default value is 0.

Groups: Number of groups of free lists for a table, cluster, or index. You can enter a value of 1 or more. The default value is 1.

Continue

Click on this button to start the actual defragmentation process.

Cancel

Click on this button to close the Defragmentation property sheet without defragmenting the selected table segment.

Help

Click on this button to display Help for the Defragmentation property sheet.

Joining Adjacent Free Extents in the Database

If you administer an active database, you may want to join adjacent free blocks in the database on a frequent basis. (If you administer an active Release 7.3 database, you should not need to use this Oracle Tablespace Manager feature, as adjacent free blocks are automatically joined on a regular basis.)

Attention:

The Coalesce menu item is enabled only when one of the following conditions exists: 1) the release of the Oracle database is 7.2 or greater; and 2) the tablespace that has been selected in the Oracle Tablespace Manager tree list contains adjacent free blocks, as shown in the Segments page graphic display.

To use Oracle Tablespace Manager to join adjacent free blocks in the database, take the following steps:

1.: In the Oracle Tablespace Manager tree list, click on the tablespace or datafile of interest.
2.: When the Segments page displays, select the segment for which you want to join adjacent free blocks.
3.: Choose Coalesce from the Tablespace menu.

Each group of adjacent free blocks in the space allocation graphic of the Segments page should now appear as a single free block.

Copyright © 1996 Oracle Corporation.
All Rights Reserved.