Performing Common Database Storage Tasks

As a DBA, you must understand how to perform the following tasks related to managing storage:

Viewing Tablespaces in Your Database
Creating a Tablespace
Modifying a Tablespace
Dropping a Tablespace
Reclaiming Wasted Space

Viewing Tablespaces in Your Database

You can view all tablespaces in your database. From the Storage section of the Administration page, click Tablespaces to open the Tablespaces page. This page shows the following information about each tablespace:

Type of tablespace
Type of extent management
Type of segment management
Status
Size
Percent of tablespace in use

Click the tablespace name for more information.

Creating a Tablespace

A user, groups of users, or an application might require its own tablespace for creating schema objects, rather than using the default USER tablespace. To create a tablespace, follow the steps listed in this section.

From Storage section of the Administration page, click Tablespaces.

The Tablespaces page appears.
Click Create to open the Create Tablespace General page.

Alternatively, to create a tablespace that is like an existing tablespace, select an existing tablespace. From the Actions list, select Create Like and then click Go. The other options that are available from the Actions menu are shortcuts for various tablespace operations.
Enter a name for the tablespace. For example, enter TB1.
In the Extent Management section, select Locally Managed. See "Locally-Managed vs. Dictionary-Managed Tablespaces" .
In the Type section, select Permanent. See "Tablespace Type".
In the Status section, select Read Write from among the status options. See "Tablespace Status".
In the Datafiles section of the page, click Add to add one or more datafiles to the tablespace. See "Datafiles".

Note:
If you check Use bigfile tablespace, then the tablespace can have only one datafile. Bigfile tablespaces are used with very large databases that use Automatic Storage Management or other logical volume managers that support striping, RAID, and dynamically extensible logical volumes.
In the Add Datafiles page, enter a file name. For example, enter TB1.dbf. Accept the File Directory and File Size defaults.
In the Storage section, select Automatically extend datafile when full (AUTOEXTEND) to allow the Oracle database to automatically expand the datafile. Specify an amount in the Increment field by which you want to extend the datafile each time it fills. Leave the Maximum File Size set to Unlimited. Click Continue to return to the Create Tablespace page.
Click Storage to open the Create Tablespace Storage property page. Leave all of the defaults settings on the Storage page. See online Help for more information on these settings.
Click OK to add the tablespace. You can now see the new tablespace in the Tablespaces page.

Modifying a Tablespace

With Enterprise Manager, you can modify a tablespace. For example, you can extend it, set it to automatically extend, change its space thresholds, or change its status to offline. When you get a critical and warning alert, you might need to extend a tablespace or take it offline to recover it.

This section shows you how to set a tablespace to automatically extend when it reaches its limit.

From the Storage section of the Administration page, click Tablespaces.

The Tablespaces page appears.
Select a tablespace to autoextend, such as USERS, and click Edit.

The Edit Tablespace: USERS General property page appears.
Select the datafile associated with the tablespace and click Edit.

The Edit Datafile page appears.
Check Automatically extend datafile when full (AUTOEXTEND). Set a suitable increment, such as 1 MB. You can optionally set a maximum file size or set file size to unlimited depending on your resources.
Click Continue to return to the Edit Tablespace page.
Click Apply.

A confirmation message appears.

Similarly, you can change tablespace thresholds by selecting a tablespace on the Tablespaces page and clicking Edit. Click Thresholds to navigate to the Threshold property page. This page lets you set monitored thresholds for space usage. You receive a warning when the threshold is reached. You can accept the default, specify different thresholds, or disable threshold warnings. See Chapter 10, "Monitoring and Tuning the Database" for more information about monitoring the database.

You can take a tablespace offline by selecting a tablespace on the Tablespaces page and clicking Edit. Select Offline on the Edit Tablespace General page and then click Apply.

Dropping a Tablespace

After a tablespace has been dropped, the objects and data in it are no longer available. To recover them can be a time-consuming process. Oracle recommends performing a backup before and after dropping a tablespace.

From the Storage section on the Administration page, click Tablespaces.

The Tablespaces page appears.
Select the tablespace that you want to drop and click Delete.

Enterprise Manager asks for confirmation that you want to delete the tablespace and associated datafiles from the operating system.
Click Yes to remove the tablespace.

Reclaiming Wasted Space

Over time, inserts, updates, and deletes (DML operations) to objects within a tablespace can create pockets of empty space that individually are not big enough to be reused. Collectively, these pockets can combine to form large amounts of wasted space. The resulting sparsely-populated objects can suffer performance degradation during queries and DML operations.

An Oracle database enables you reclaim wasted space in the following ways:

Shrink Operation

Data is compacted to the front of the table. Free space can either be returned to the tablespace or kept in the table for future inserts. A shrink operation on a table does not affect DML operations to the table during the operation.

Reorganization Operation

The object is re-created in a different part of the tablespace. This operation has the desirable side effect of compacting the object. For this operation to succeed, however, the tablespace must already have free space equal to the size of the object.

Viewing Segment Advisor Recommendations

The Segment Advisor identifies objects such as tablespaces and tables that have unused space you can reclaim. It performs its analysis by examining usage and growth statistics and by sampling the data in the object. It is configured to run automatically at regular intervals, and you can also run it manually.

The regularly scheduled Segment Advisor run is known as the Automatic Segment Advisor. Results from the advisor are summarized on the Space Summary section of the home page as Segment Advisor Recommendations. See Figure 6-2, "Home Page Space Summary". Click the adjacent link to open the Segment Advisor Recommendations page.

Figure 6-2 Home Page Space Summary

Description of "Figure 6-2 Home Page Space Summary"

Objects such as tablespaces, tables, and indexes are made up of one or more segments. The Segment Advisor page summarizes space usage, estimating the amount of reclaimable space for each tablespace. From this page you can drill down to the segment level. See Figure 6-3, "Segment Advisor Recommendations".

Recommendations can be either a shrink or reorganization operation. If you created the tablespace with automatic segment space management, which is the default, then the Segment Advisor recommends shrinking. If the segment is not eligible for shrink, or if the tablespace was created with manual segment space management, then the Segment Advisor recommends other reorganizing methods.

The following section describes how to view the recommendations and then shrink your objects using the shrink wizard.

To view recommendations and shrink an object:

From the Space Summary section of the Administration page, click the link adjacent to Segment Advisor Recommendations. Figure 6-2, "Home Page Space Summary" shows the link.

The Segment Advisor Recommendations page appears, as shown in Figure 6-3, "Segment Advisor Recommendations".

Figure 6-3 Segment Advisor Recommendations

Description of "Figure 6-3 Segment Advisor Recommendations"
Select a tablespace and then click Recommendation Details.

The Recommendation Details for Tablespace page appears, as shown in Figure 6-4, "Segment Advisor Recommendation Details". This page lists the tablespace segments that are eligible for shrinking.

Figure 6-4 Segment Advisor Recommendation Details

Description of "Figure 6-4 Segment Advisor Recommendation Details"
Select a segment to shrink by using either of the following techniques:
- Click Shrink next to the segment you want to shrink.
- Select one or more segments and click Implement.
The Shrink Segment: Options page appears.

You can also reorganize an object at this step. To do so, click the Reorg button and continue with the reorganization wizard.
Accept Compact Segments and Release Space. This action returns freed space to the tablespace. If you do not want to release the freed space to the tablespace, then choose Compact Segments. Click Implement.

The Shrink Segment: Schedule page appears.
Note your job name. Under Start, select to run the job Immediately. The shrink operation can be resource intensive, so Oracle recommends running it during off-peak hours. You can also select Later and schedule a better time to run the operation. Click Submit.

The scheduler Jobs page appears and shows the status of the job.
Click Refresh to update the page until the status of the job is Completed. You can also click View Job Status.

Running the Segment Advisor Manually

You can run the Segment Advisor manually. You can do so when you want to analyze objects not selected by the Automatic Segment Advisor, or when you want more up-to-date recommendations on a tablespace.