Release 1.6
A63731-01
Library |
Product |
Contents |
Index |
| Oracle
Enterprise Manager Administrator's Guide
Release 1.6 A63731-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 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-10.
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.
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-15.
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.
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.
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.
It is recommended that 0 be entered to obtain extents of uniform size and to avoid fragmentation.
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-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.
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.
You need to remove any OS files manually.
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-16.
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 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.
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.
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.
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-19.
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.
