CHAPTER 8. Managing Database Storage

• Starting Storage Manager

• Tablespaces Container

• Tablespaces Multi-Column List

• Tablespaces Menu Options

• Datafiles Container

• Datafiles Multi-Column List

• Datafiles Menu Options

• Rollback Segments Container

• Rollback Segments Multi-Column List

• Rollback Segments Menu Options

Starting Storage Manager

• If you have already connected to a database, the Storage Manager appears.

• If a valid database connection has not been made, the Connect dialog box displays. This dialog box also appears when you launch Storage Manager by itself. See "Connecting to an Instance" .

After the Storage application has successfully connected to a database, the Tablespaces, Rollback Segments, and Datafiles containers display in a tree list on the left side of the Storage window. These containers are contained in the database container 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, property sheet.

For information about:

• Application containers, see page 7 - 3.

• Multi-column scrolling lists, see page 7 - 10.

• Dialog boxes, see page 7 - 11.

• Property sheets, see page 7 - 12.

Storage Menus

The Storage application has four standard menus, File, View, Log and Help, plus the 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" .

Tablespaces Container

When you select:

• The Tablespaces object type container, 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.

Tablespaces Multi-Column List

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.

Tablespaces Menu Options

• 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

• Make Writeable

• Backup Wizard to run a backup wizard that creates a tablespace backup script.

Creating a Tablespace

• General

• Extents

Create Tablespace: General Page

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.
	If the tablespace status is Offline, you can click the Online button to change the status to online.
	If the the database is version 7.1 or later, a Read Only option becomes available. If a tablespace has read-only status, the Make Writeable menu option is enabled in the Tablespace menu. 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 page 8 - 9.
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 page 8 - 10.
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 page 8 - 10.
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 commited to a tablespace.

Create Tablespace: Extents Page

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

Altering a Tablespace

The Alter/Quickedit Tablespace property sheet is the same as the Create Tablespace property sheet except as noted above. See "Creating a Tablespace" .

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

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

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

Adding a Rollback Segment to a Tablespace

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 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.
	Use the SQL Worksheet to perform media recovery on a specific tablespace or datafile. For information about the SQL Worksheet, see page 13 - 2, "Overview of the SQL Worksheet."

Make Read-Only

Make Writeable

Creating a Tablespace Backup Script

The Backup Wizard prompts you for information about the following areas:

• status of database operation that will be required in order to complete the backup.

• particular tablespaces to ba backed up.

• backup medium and system parameters..

• filename of the backup script to be generated.

Datafiles Container

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

Datafiles Multi-Column List

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

Datafiles Menu Options

• Create to create a new datafile

• Create Like to create a new datafile based on an existing datafile

• Place Online

• Take Offline

Creating a Datafile

• General

• Auto Extend

General Page

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.

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

Use Existing File	Designates that the datafile already exists and should be reused.
New 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.
Status	Status of the datafile. To change 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 change the status to Offline.

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

Auto Extend Page

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

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

Place Online

Take Offline

The Rollback Container

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

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.

Rollback Menu Options

• 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

• Take Offline

Creating a Rollback Segment

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

• General

• Extents

Create Rollback Segment: General Page

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	Status of the rollback segment to be created. Newly created segments default to Offline status.
	If the status is Offline, you can click the Online button to change the status to online.
	If the status is Online, you can click the Offline button to change the status to offline.

Create Rollback Segment: Extents Page

The Storage 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

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

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

Shrinking a 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:

Shrink to	Optimal Size: Optimal size of rollback segments.
	Size: Specify a specific number of bytes in active extents in the rollback segment.

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

Changing a Rollback Segment to Online or Offline Status

To take a rollback segment offline, select the rollback segment from the tree list and 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.