Oracle Enterprise Manager Administrator's Guide Release 1.4.0 A53702_01 |
|
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:
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 the 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.
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:
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-8.
The Tablespace menu allows you to perform the following operations:
Creates a new tablespace.
Removes an existing tablespace.
Displays database object(s) that rely on a selected tablespace and object(s) that selected tablespace relies on.
Adds a datafile to a tablespace.
Adds a rollback segment to a tablespace.
Places a tablespace online.
Makes a tablespace read-only.
Makes a tablespace read/write.
These menu options are enabled depending on the object selected in the tree list.
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:
Creates a new datafile.
Creates a new datafile based on parameter settings of an existing datafile.
Displays database objects that rely on a selected rollback segment and any objects that the selected rollback segment relies on.
Places a datafile online.
Takes a datafile Offline.
The Rollback Segment menu allows you to perform the following operations:
Creates a new rollback segment.
Creates a new rollback segment based on an existing rollback segment.
Displays database object(s) that rely on a selected rollback segment and any object(s) that selected rollback segment relies on.
Removes an existing rollback segment.
Shrinks an existing rollback segment (Oracle 7.2 or later)
Places a rollback segment online.
Takes a rollback segment offline.
These menu options are enabled depending on the object selected in the tree list.
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.
The Tablespace multi-column list displays when the Tablespace folder is selected in tree list. The columns of the list are:
Name of the tablespace.
Status of the tablespace: Online, Offline, or Read-only.
Total size in megabytes of the datafiles that comprise the tablespace.
Amount of space in megabytes used in the 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:
The General page of the Create Tablespace property sheet contains fields that define the tablespace. These fields are described below:
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 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.
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-11.
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-12.
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-12.
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.
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.
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:
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."
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.
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.
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.
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 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.
To display tablespace dependencies and dependents:
To alter an existing tablespace:
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-6.
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.
To drop an existing tablespace:
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.
To add a datafile to an existing tablespace:
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.
To add a rollback segment to an existing tablespace:
You can also add a rollback segment to a tablespace through the Create Rollback Segment property sheet.
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.
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.
To change a writeable tablespace to read-only status:
You can also change the tablespace status from the Quick Edit Tablespace property sheet.
To change a read-only tablespace to writeable status:
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.
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.
The columns of the Datafile multi-column list are described below:
Name of the datafile and the path (truncated by ellipses if the path is too long).
Tablespace to which the datafile belongs.
Status of the datafile: Online or Offline.
Size of the datafile in megabytes.
Amount (in megabytes) of data filling the datafile. Displayed as a bar chart.
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.
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 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.
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.
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.
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.
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:
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.
Determines the size and units of the increment size.
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.
To create a new datafile with parameters set like an existing datafile:
The Create Datafile property sheet appears with all parameters set except the name. See Creating a Datafile on page 8-12.
To edit an existing datafile:
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.
To place an existing datafile online:
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.
To take and existing datafile offline:
Attention: When adding a datafile to either a new or existing tablespace, the Online and Offline option will be disabled.
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.
The columns of the Rollback multi-column list are described below:
Name of the rollback segment.
Tablespace that contains the rollback segment.
Status of the rollback segment: ONLINE, Offline, Needs Recovery, or Partly Available.
Space allocated in megabytes to the rollback segment.
The percentage of the datafile that has ever been filled with data. Displayed as a notch on a bar chart.
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:
The General page of the Create Rollback Segment property sheet is described below:
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.
Name of the tablespace in which to create the rollback segment.
Choose the tablespace from the drop-down list.
Toggles between public and private rollback segments. A private rollback segment acquired explicitly by an instance when the instance opens the database. A public rollback segment forms a pool of rollback segments that any instance requiring rollback segments can use.
Online: Specifies that the rollback segment be placed online.
Offline: Specifies that the rollback segment be place offline.
On the Extents page you can specify the storage characteristics of the rollback segment. The Extents page contains the following fields:
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.
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.
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 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.
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 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.
To alter an existing rollback segment:
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-15.
Note: Any changes you make on the Storage page apply to any subsequent extent allocations to the rollback segment, not existing extents.
To drop an existing rollback segment:
Attention: You can only drop a rollback segment that is offline.
To shrink an existing rollback segment:
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:
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:
To place a rollback segment online:
To take a rollback segment 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.