Oracle Enterprise Manager Administrator's Guide

Library

Product

Contents

Index

Managing Database Storage

Starting Storage Manager
Storage Menus
Tablespaces Folder
Datafiles Folder
Rollback Folder

This chapter describes how to use Storage Manager to administer tablespace, rollback segment, and datafile storage in a database. This chapter assumes that you have read Chapter 7, Overview of the Database Tools and are familiar with the interface elements of the database tools. The topics included in this chapter are:

Starting Storage Manager

To start Storage Manager, select a database object from the navigator and click on the Storage icon in the Launch Palette or choose Storage from the Launch menu.

If you have already connected to a database, the Storage Manager appears.
If a valid database connection has not been made, the Login information dialog box displays. This dialog box also appears when you launch Storage Manager by itself. See Connecting to an Instance on page 1-17.

Note:
You can change the database connection with the Change Database Connection option in the File menu of the console.

After the Storage Manager Connects to a database, the Tablespaces, Rollback Segments, and Datafiles folders display in a navigator on the left side of the Storage window. These folders are contained in the database folder which displays the name of the database that the application is connected to.

Figure 8-1: The Storage Manager

The display on the right side of the window is determined by the objects selected on the left side of the screen. The right side may contain a multi-column scrolling list or a property sheet.

For more information, see:

Storage Menus

The Storage application has four standard menus, File, View, Log, and Help, plus Tablespace, Datafile, and Rollback. The options for the specific Storage menus are described in this chapter. For information on the standard menus, see Application Menus on page 7-7.

Tablespace Menu

The Tablespace menu allows you to perform the following operations:

Create to create a new tablespace.
Remove an existing tablespace.
Add Datafile to add a datafile to a tablespace.
Add Rollback Segment to add a rollback segment to a tablespace
Place Online to place a tablespace online.
Take Offline (Normal, Temporary, Immediate)
Make Read-Only to make a tablespace read-only.
Make Writeable to make a read-only tablespace writeable.
Backup Wizard to run a backup wizard that creates a tablespace backup script.

Datafile Menu

The Datafile menu allows you to perform the following operations.

Create to create a new datafile
Create Like to create a new datafile based on an existing datafile
Place Online to place a datafile selected in the navigator online.
Take Offline to take a datafile selected in the navigator offline.

Rollback Menu

The Rollback Segment menu allows you to perform the following operations:

Create to create a new rollback segment
Create Like to create a new rollback segment based on an existing rollback segment
Remove an existing rollback segment
Shrink an existing rollback segment (7.2 or later Oracle databases)
Place Online to place a rollback segment online.
Take Offline to take a selected rollback segment offline.

Note:
Note:Menu options are enabled depending on the object selected in the navigator.

Tablespaces Folder

The Tablespaces object type folder contains all the tablespaces in the database arranged alphabetically in the navigator tree list. An individual tablespace can be expanded to show the datafiles and rollback segments in the tablespace.

When you select:

The Tablespaces object type folder, a multi-column list displays a row of summary information for each of the tablespaces in the database.
An individual tablespace, a property sheet displays the detailed settings for the tablespace.

For information about managing tablespaces, see the Oracle7 Server Concepts, the Oracle7 Server Administrator's Guide, and the Oracle7 Server SQL Reference.

Tablespaces Multi-Column List

The Tablespace multi-column list displays when the Tablespace folder is selected in navigator. The columns of the list are:

Tablespace
Name of the tablespace.

Status
Status of the tablespace: Online, Offline, or Read-only.

Size (M)
Total size in megabytes of the datafiles that comprise the tablespace.

Used (M)
Amount of space in megabytes used in the tablespace.

Creating A Tablespace

To create a new tablespace, choose Create from the Tablespace menu. The Create Tablespace property sheet appears. The Create Tablespace property sheet consists of the following pages:

General (Basic UI mode)
Extents (Advanced UI mode)

General

The General page of the Create Tablespace property sheet contains fields that define the tablespace. These fields are described below:

Name
Name of the tablespace to be created.

Enter the name of the new tablespace. The tablespace name can only contain characters from your database character set and can be at most 30 characters long.

Status
Status of the tablespace to be created. To select the status, click the Change Status button.

If the status is Offline, click the button to change the status to Online.
If the status is Online, click the button to display a pop-up menu with Offline Normal, Offline Immediate, or Offline Temporary options. Select one of the Offline options.
If the status is Online and the database is version 7.1 or later, a Read-Only option is also available. If a tablespace has read-only status, the Make Writeable menu option is enabled. When you select the Make Writeable menu option, the tablespace becomes both readable and writeable and the status is changed to online.

Note:
The Read-Only option is disabled if there any open transactions in the database or any active rollback segments in the tablespace.

If the tablespace status is Online, you can click the Offline button to change the status to offline with Offline Normal, Offline Immediate, or Offline Temporary options. Select one of the Offline options.

Datafiles
Multi-column scrolling list of the datafiles belonging to the tablespace. The columns include Name, Status. Size, and % Full. These are the same columns as those in the Datafiles multi-column list. See Datafiles Multi-Column List on page 8-10.

Add
Displays the Create Datafile property sheet, which allows you to specify each new datafile belonging to the new tablespace. When you create a new datafile, the status column in the datafile list reads NEW. For a description of the Create Datafile property sheet, see Creating a Datafile on page 8-11.

Edit
Displays the Quick Edit Datafile property sheet, which allows you to edit the file specification for the datafile selected in the Datafiles scrolling list. You can also double-click on a datafile in the Datafiles scrolling list to display the Quick Edit Datafile property sheet.

This property sheet is the same as the Create Datafile property sheet. For a description of the Create Datafile property sheet, see Creating a Datafile on page 8-11.

Remove
Removes the datafile selected in the Datafiles scrolling list. You can also press the Delete key to remove a selected datafile. You can only remove a datafile that has been newly created and has not been committed to a tablespace.

Type
Permanent: Specifies that the tablespace will be used to hold permanent database objects. This option is selected by default.

Temporary: Specifies that the tablespace will only be used to hold temporary objects (sort segments). No permanent object can reside in a temporary tablespace.

Attention:
This option is only available for Oracle version 7.3 or greater. For more information on temporary tablespaces and their application, see Oracle7 Server Concepts.

Extents

On the Extents page, you can specify the default storage parameters for all objects created in the tablespace. The Extents page contains the following fields:

Override Default Values
If this box is checked, you can edit all of the fields on the Extents page. If the box is not checked, the default value for each field displays and the values cannot be modified. This checkbox is only enabled during tablespace creation.

Note:
If you do not enter an integral multiple of the operating system data block size when defining the size of extents, your entry is increased to the next multiple. If you do not enter a value, the default value remains. If a default value has not been explicitly specified, the field contains "Default."

Initial Size
Size of the object's first extent.

Enter the size of the initial extent. Use the unit button to specify either kilobytes or megabytes. If you do not specify a size, the default is the size of 5 data blocks.

Next Size
Size of the next extent to be allocated to the object.

Enter the size of the next extent. Use the unit button to specify either kilobytes or megabytes. If you do not specify a size, the default is the size of 5 data blocks.

Increase Size By
Percent by which each extent after the second grows over the previous extent.

Enter a value for percent increase. If you do not specify a value, the default is 50.

Minimum Number
Total number of extents to be allocated when an object is created in the tablespace.

Enter the minimum number of extents. If you do not specify a number, the default value is 1.

Maximum Number
Maximum number of extents that can be allocated to an object created in the tablespace.

Unlimited: When selected, allows you to create a number of extents that is only limited by the amount of contiguous free space in a tablespace.

Value: When selected, allows you to specify the maximum number of extents. If you do not specify a value, the default value applies. The default and maximum values depend on the data block size.

Editing a Tablespace

To alter an existing tablespace:

Select a tablespace in the Tablespace multi-column list using the right mouse button. This displays the context-sensitive menu.

2.

Choose the Quick Edit menu option. The Quick Edit Tablespace property sheet appears.

3.

Click Apply.

: You can also display tablespace information by clicking on a tablespace in the Storage Manager navigator.

The Quick Edit Tablespace property sheet contains the same elements as the Create Tablespace property sheet except as noted above. See Creating A Tablespace on page 8-5.

Attention:
If you alter an object, such as a datafile named DATA1, at any location in the navigator, all instances of the object in the tree are changed.

Dropping a Tablespace

To drop an existing tablespace:

1.: Select the tablespace to be dropped from the Tablespace navigator.
2.: Choose Remove from the Tablespace menu. The Remove Tablespace alert box appears.
3.: Click Yes.

When you drop a tablespace, all objects in the tablespace are dropped as well. Storage Manager also drops all referential integrity constraints from tables outside the tablespace that refer to primary or unique keys in the tables stored in the dropped tablespace.

Suggestion:
Before dropping a tablespace, take it offline. This ensures that SQL statements that are currently running transactions do not access objects in the tablespace.

Adding a Datafile to a Tablespace

To add a datafile to an existing tablespace:

1.: Select the tablespace from the Tablespace object list.
2.: Choose Add Datafile from the Tablespace menu. The Create Datafile property sheet appears. See Creating a Datafile on page 8-11.
3.: Select the desired tablespace from the drop-down menu and fill out the rest of the property sheet parameters.
4.: Click the Create button.

You can also add a datafile to a tablespace through the Quick Edit/Create Tablespace property sheet or the Create Datafile property sheet.

Attention:
When adding a datafile to either a new or existing tablespace, the Online and Offline option will be disabled.

Adding a Rollback Segment to a Tablespace

To add a rollback segment to an existing tablespace:

1.: Select the tablespace from the Tablespace object list.
2.: Choose Add Rollback Segment from the Tablespace menu. The Create Rollback Segment property sheet appears. See Creating a Rollback Segment on page 8-14.
3.: Click Create.

You can also add a rollback segment to a tablespace through the Create Rollback Segment property sheet.

Changing a Tablespace to Online or Offline Status

To place a tablespace online, select the tablespace from the Tablespace object list and choose Place Online from the Tablespace menu. The tablespace is placed online.

To take a tablespace offline, select the tablespace from the Tablespace object list and choose one of the Take Offline cascading menu options: Normal, Temporary, or Immediate.

Take Offline
Normal: Takes the tablespace offline in normal mode.

A checkpoint is performed for all datafiles in the tablespace (all of these datafiles must be available). You need not perform media recovery on this tablespace before placing it back online. You must use this option if the database is in NOARCHIVELOG mode.

Temporary: Takes the tablespace offline in temporary mode.

A checkpoint is performed for all online datafiles in the tablespace but does not ensure that all files can be written. Any offline files may require media recovery before you place the tablespace back online.

Immediate: Takes the tablespace offline in immediate mode.

Oracle does not ensure that the datafiles are available, and no checkpoint is performed. You must perform media recovery on the tablespace before placing it back online.

Making a Tablespace Read-Only

To change a writeable tablespace to read-only status:

1.: Select the tablespace from the navigator.
2.: Choose Make Read-Only from the Tablespace menu.

You can also change the tablespace status from the Quick Edit Tablespace property sheet.

Making a Tablespace Writeable

To change a read-only tablespace to writeable status:

1.: Select the tablespace from the navigator
2.: Choose Make Writeable from the Tablespace menu.

You can also change the tablespace status from the Quick Edit Tablespace property sheet that appears when you select a tablespace from the navigator.

Creating a Tablespace Backup Script

To backup one or more tablespaces of a database, you can choose the Backup Wizard menu option from the Tablespace menu. This starts up a wizard that prompts you for backup information and then generates a backup job script for submission to the Oracle Enterprise Manager job system.

status of database operation that will be required in order to complete the backup.
particular tablespaces to be backed up.
backup medium and system parameters.
filename of the backup script to be generated.

Datafiles Folder

The Datafiles folder contains information about the datafiles in the database. The listing of the datafiles is arranged in a tree structure that can be expanded to show individual datafiles along with their respective path information.

For information about datafiles, see the Oracle7 Server Concepts and the Oracle7 Server Administrator's Guide.

Datafiles Multi-Column List

The columns of the Datafile multi-column list are described below:

Name
Name of the datafile and the path (truncated by ellipses if the path is too long).

Tablespace
Tablespace to which the datafile belongs.

Status
Status of the datafile: Online or Offline.

Size (M)
Size of the datafile in megabytes.

Used (M)
Amount (in megabytes) of data filling the datafile. Displayed as a bar chart.

Creating a Datafile

To create a new datafile, choose Create from the Datafile menu. The Create Datafile property sheet appears.

The Create Datafile property sheet contains the following pages:

General
Auto Extend (Advanced UI mode)

Note:
The Auto Extend page only appears if the database is version 7.2 or later.

General

The General page of the Datafile property sheet allows you to modify existing datafile parameters or create a new datafile when the Create menu option is selected from the Datafile menu.The General page of the Create Datafile property sheet is described below:

Name
Name of the datafile to be created.

Enter the file and path name of the new datafile. The filename must be specified according to the conventions of your operating system.

When altering an existing datafile, you can rename the datafile by typing in a new name.

Attention:
You must rename a datafile if you have changed the name of the corresponding operating system file or if you have moved the file to a new location. To rename a datafile, its tablespace must be offline.

Attention:
When you rename a datafile using the Storage application, the name of the operating system file is not changed. The new filename is only associated with the tablespace. Before renaming the datafile using the Storage application, you must change the name of the file through your operating system.

Tablespace
Name of the tablespace to which the new datafile belongs. Use the drop-down list to choose the tablespace. You can only choose the tablespace when the datafile is created.

Status
Online: Specifies that the datafile be placed online.

Offline: Specifies that the datafile be place offline.

Note:
When adding a datafile to either a new or existing tablespace, the Online and Offline option will be disabled.

Note:
When altering a tablespace, the fields on this page cannot be modified if the datafile belongs to a read-only tablespace.

To determine the file size, select either the Use Existing File or New File Size option.

Size
File Size: Allows you to designate the file size of a new or existing file. Enter the size of the new or existing datafile. Use the unit buttons to specify either kilobytes or megabytes.

Reuse Existing File: Designates that the datafile already exists and should be reused.

Note:
When altering a tablespace, the fields on this page cannot be modified if the datafile belongs to a read-only tablespace.

Auto Extend

The AutoExtend page (available in Advanced UI mode) sets the Auto Extend feature for a datafile used in a database that is version 7.2 or later. The page consists of the following:

Enable Auto Extend
Determines whether the Auto Extend feature is enabled or disabled. Disabled is the default setting. If the box is not checked, the other fields on the page are disabled.

Increment
Determines the size and units of the increment size.

Maximum Extent
Determines the size of the maximum extent. You can select the Unlimited button to set the maximum extent size to Unlimited or select Value to specify the size and units. Unlimited is selected by default.

Note:
If the datafile belongs to a read-only tablespace, all the fields on this page are disabled.

Creating a Datafile Like an Existing Datafile

To create a new datafile with parameters set like an existing datafile:

1.: Select a datafile from the navigator.
2.: Choose Create Like from the Datafile menu.

The Create Datafile property sheet appears with all parameters set except the name. See Creating a Datafile on page 8-11.

Editing an Existing Datafile

To edit an existing datafile:

1.: Select the datafile from the navigator. The Datafile property sheet displays with all parameters set except the name. The property sheet is the same as the Create Datafile property sheet. See Creating a Datafile on page 8-11.
2.: Modify the Datafile property sheet as desired.

You can also alter an existing datafile by selecting a datafile from the multi-column list using the right mouse button and then choosing Quick Edit from the context-sensitive menu.

Placing a Datafile Online

To place an existing datafile online:

1.: Select a datafile from the navigator.
2.: Select the Place Online menu option from the Datafile menu.

You can also change the online/offline status of the datafile by selecting the desired datafile and applying the change from the Datafile property sheet.

Taking a Datafile Offline

To take and existing datafile offline:

1.: Select the desired datafile from the navigator
2.: Select the Take Offline menu option from Datafile menu.

Attention:
When adding a datafile to either a new or existing tablespace, the Online and Offline option will be disabled.

Rollback Folder

The Rollback Segments folder contains information about the rollback segments in the database. The listing of the rollback segments is arranged in a tree structure that can be expanded to show individual rollback segments.

For information about managing rollback segments, see the Oracle7 Server Concepts, the Oracle7 Server Administrator's Guide, and the Oracle7 Server SQL Reference.

Rollback Multi-Column List

The columns of the Rollback multi-column list are described below:

Segment
Name of the rollback segment.

Tablespace
Tablespace that contains the rollback segment.

Status
Status of the rollback segment: ONLINE, Offline, Needs Recovery, or Partly Available.

Size (M)
Space allocated in megabytes to the rollback segment.

Highwater Mark
The percentage of the datafile that has been filled with data. Displayed as a notch on a bar chart.

Creating a Rollback Segment

To create a new rollback segment, choose Create from the Rollback menu. The Create Rollback Segment property sheet appears.

The Create Rollback Segment property sheet consists of the following pages:

General
Extents (Advance UI mode)

General

The General page of the Create Rollback Segment property sheet is described below:

Name
Name of the rollback segment to be created.

Enter the name of the new rollback segment. The rollback segment name can only contain characters from your database character set and can be at most 30 characters long.

Tablespace
Name of the tablespace in which to create the rollback segment.

Choose the tablespace from the drop-down list.

Status
Online: Specifies that the rollback segment be placed online.

Offline: Specifies that the rollback segment be place offline.

Extents

On the Extents page you can specify the storage characteristics of the rollback segment. The Extents page contains the following fields:

Initial Size
Size of the rollback segment's first extent.

Enter the size of the initial extent. Use the unit button to specify either kilobytes or megabytes. The default is the size of 5 data blocks.

Next Size
Size of the next extent allocated to the rollback segment.

Enter the size of the next extent. Use the unit button to specify either kilobytes or megabytes. The default is the size of 5 data blocks.

Optimal Size
Optimal size for the rollback segment. Optimal is not displayed for offline rollback segments.

Enter the value for Optimal. Use the unit button to specify either kilobytes or megabytes. Oracle tries to maintain the optimal size of the rollback segment by dynamically deallocating extents when their data is no longer needed for active transactions. A blank field assumes the default value.

The default value of Optimal is null. If Optimal is null, Oracle never deallocates the rollback segment's unused extents.

The value of Optimal can never be less than the space initially allocated to the rollback segment, as specified by the values of Initial Extent, Next Extent, and Minimum Extents.

Minimum Number
Total number of extents to be allocated when the rollback segment is created. This field is only enabled when creating a rollback segment.

Enter the minimum number of extents. The default and minimum value is 2.

Maximum
Maximum number of extents that can be allocated to the rollback segment. The default is operating system dependent.

Enter the maximum number of extents. The default and maximum values depend on the data block size.

Altering a Rollback Segment

To alter an existing rollback segment:

1.: Select the rollback segment from the navigator. The Rollback property sheet appears.
2.: Modify the property sheet settings as desired.
3.: Click Apply to implement the changes.

You can also perform this operation using the Quick Edit popup menu option by selecting the rollback segment from the multi-column list using the right mouse button. The Quick Edit Rollback Segment property sheet is the same as the Create Rollback Segment property sheet. See Creating a Rollback Segment on page 8-14.

Note:
Any changes you make on the Storage page apply to any subsequent extent allocations to the rollback segment, not existing extents.

Dropping a Rollback Segment

To drop an existing rollback segment:

1.: Select the rollback segment to be dropped from the multi-column list.
2.: Choose Remove from the Rollback menu. The Remove Rollback Segment alert box appears.
3.: Click Yes.

Attention:
You can only drop a rollback segment that is offline.

Shrinking a Rollback Segment

To shrink an existing rollback segment:

1.: Select the rollback segment from the navigator.
2.: Choose Shrink from the Rollback menu. The Shrink Rollback Segment dialog box appears.
3.: Click OK.

You can also display the Shrink Rollback Segment property sheet by selecting a rollback segment from the multi-column list and choosing Shrink from the context-sensitive menu.

The Shrink Rollback Segment dialog box contains the following information:

Shrink to
Optimal Size: Shrink the rollback segment to an optimal size. The optimal value is determined by the value of the STORAGE parameter set when you originally created the rollback segment.

Size: Specify the number of bytes (K or M) in active extents in the rollback segment.

Attention:
You can only shrink a rollback segment that is online.

The amount of rollback segment shrinkage depends on the following factors:

available free space in the rollback segment.
how active transactions are holding space in the rollback segment.

Changing a Rollback Segment to Online or Offline Status

To place a rollback segment online:

1.: Select the rollback segment from the navigator.
2.: Choose Place Online from the Rollback menu. The rollback segment is placed online.

To take a rollback segment offline:

1.: Select the rollback segment from the navigator.
2.: Choose Take Offline from the Rollback menu. The rollback segment is taken offline.

When you change the status of a rollback segment to offline, Oracle takes the rollback segment offline immediately if the rollback segment does not contain information necessary to roll back any active transactions. If the rollback segment does contain information for active transactions, Oracle makes the rollback segment unavailable for future transactions and takes it offline after all the active transactions are committed or rolled back.

Attention:
Because Oracle does not take a rollback segment offline until all its active transactions have completed, there may be some delay before the status of the rollback segment is changed to Offline in the Rollback object list. Pending Offline displays in the object list to indicate that the rollback segment was taken offline while it was busy.

Prev Next

Library

Product

Contents

Index