Oracle Enterprise Manager Administrator's Guide 
Release 1.6 
A63731-01
 
Library
 
Product
 
Contents
 
Index
 

Prev Next

8
Managing Database Storage

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 tree list and click on the Storage icon in the Launch Palette or choose Storage Manager from the Console Tools menu.

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

After Storage Manager connects to a database, the Tablespaces, Rollback Segments, and Datafiles folders display in a tree list 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 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-10.

Tablespace Menu

The Tablespace menu allows you to perform the following operations:

Create

Creates a new tablespace.

Remove

Removes an existing tablespace.

Show Dependencies

Displays database object(s) that rely on a selected tablespace and object(s) that selected tablespace relies on.

Add Datafile

Adds a datafile to a tablespace.

Add Rollback Segment

Adds a rollback segment to a tablespace.

Place Online

Places a tablespace online.

Take Offline (Normal, Temporary, Immediate)
Make Read-Only

Makes a tablespace read-only.

Make Writeable

Makes a tablespace read/write.

These menu options are enabled depending on the object selected in the tree list.

Datafile Menu

When you select the Datafiles container or a datafile in the container, various menu options in the Datafile menu are enabled. Depending on what objects are selected in the tree list, the Datafile menu allows you to perform the following operations:

Create

Creates a new datafile.

Create Like

Creates a new datafile based on parameter settings of an existing datafile.

Show Dependencies

Displays database objects that rely on a selected rollback segment and any objects that the selected rollback segment relies on.

Place Online

Places a datafile online.

Take Offline

Takes a datafile Offline.

Rollback Menu

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

Create

Creates a new rollback segment.

Create Like

Creates a new rollback segment based on an existing rollback segment.

Show Dependencies

Displays database object(s) that rely on a selected rollback segment and any object(s) that selected rollback segment relies on.

Remove

Removes an existing rollback segment.

Shrink

Shrinks an existing rollback segment (Oracle 7.2 or later)

Place Online

Places a rollback segment online.

Take Offline

Takes a rollback segment offline.

These menu options are enabled depending on the object selected in the tree list.

Tablespaces Folder

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

When you select:

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

Tablespaces Multi-Column List

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

Name

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:

Tablespace Property Sheet: General Page

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 on the desired option.

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 (M), and Used (M). These are the same columns as those in the Datafiles multi-column list. See Datafiles Multi-Column List on page 8-15.

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-16.

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-16.

Remove

Removes the datafile selected in the Datafiles scrolling list. 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, see Oracle Server Concepts.

Tablespace Property Sheet: Extents Page

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.

Note:

It is recommended that 0 be entered to obtain extents of uniform size and to avoid fragmentation.

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.

Showing Tablespace Dependencies

To display tablespace dependencies and dependents:

  1. Select a tablespace from the tree list.
  2. Choose Show Dependencies from the Tablespace menu. The Dependencies Viewer displays.

Editing a Tablespace

To alter an existing tablespace:

  1. 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. Modify the property sheet parameters as necessary.
  4. Click Apply.
  5. You can also display tablespace information by clicking on a tablespace in the Storage Manager tree list.

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-7.

Attention: If you alter an object, such as a datafile named DATA1, at any location in the tree list, 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 tree list.
  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.

Note:

You need to remove any OS files manually.

Adding a Datafile to a Tablespace

To add a datafile to an existing tablespace:

  1. Select the tablespace from the Tablespace object list..
  2. Select the desired tablespace from the drop-down menu and fill out the rest of the property sheet parameters.
  3. 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-19.
  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 tree 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.

You can also perform these tasks from the General page of the Tablespace property sheet.

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 tree list.
  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 tree list
  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 tree list.

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 Oracle Server Concepts and the Oracle 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:

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

Datafile Property Sheet: General Page

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.

Datafile Property Sheet: Auto Extend Page

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 tree list.
  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-16.

Editing an Existing Datafile

To edit an existing datafile:

  1. Select the datafile from the tree list. 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-16.
  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 tree list.
  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 tree list
  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 Segments 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 Oracle Server Concepts, the Oracle Server Administrator's Guide, and the Oracle Server SQL Reference.

Rollback Multi-Column List

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

Name

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.

High Water Mark (M)

The percentage of the datafile that has ever 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:

Create Rollback Segment Property Sheet: General Page

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.

Public

Toggles between public and private rollback segments. A private rollback segment is acquired explicitly by an instance when the instance opens the database (Parallel Server option). A public rollback segment forms a pool of rollback segments that any instance requiring rollback segments can use.

Status

Online: Specifies that the rollback segment be placed online.

Offline: Specifies that the rollback segment be place offline.

Rollback Segment Property Sheet: Extents Page

On the Extents page you can specify the storage characteristics of the rollback segment. 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 rollback Segment creation.

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

Unlimited: Specifies that an unlimited number of extents can be allocated to the rollback segment.

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

Note:

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 tree list. 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-19.

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 tree list.
  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:

Changing a Rollback Segment to Online or Offline Status

To place a rollback segment online:

  1. Select the rollback segment from the tree list.
  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 tree list.
  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
 
Oracle 
Copyright © 1998 Oracle Corporation. 
All Rights Reserved. 
 
Library
 
Product
 
Contents
 
Index