6 Managing Database Storage Structures

This chapter discusses using Enterprise Manager to explore and manage the storage structures of your database. This chapter contains the following topics:

Exploring the Storage Structure of Your Database

An Oracle database is made up of physical and logical structures. Physical structures are those that can be seen and operated on from the operating system, such as the physical files that store data on disk.

Logical structures are created and recognized by the Oracle database server and are not known to the operating system. The primary logical structure in a database, a tablespace, contains physical files. The applications developer or end user may be aware of the logical structure, but is not usually aware of this physical structure. The DBA must understand the relationship between the physical and logical structures of a database.

The Oracle database server can automate the management of its structure. Furthermore, Oracle Enterprise Manager provides a Web-based GUI to enable easy management and monitoring of your database.

To view database storage structure, navigate to the Storage section of the Administration Home page (see Figure 5-2, "Database Administration Page"). You can click the links shown in Figure 6-1 to access the storage pages.

Figure 6-1 Storage Options

Description of Figure 6-1 follows
Description of "Figure 6-1 Storage Options"

The following sections provide a closer look at the database storage structure:

Control Files

A control file tracks the physical components of the database. It is essential to the functioning of the database. Because of the importance of the control file, Oracle recommends that the control file be multiplexed. In other words, the control file should have multiple identical copies. For databases created with DBCA, three copies of the control file are automatically created and kept in sync with each other.

If any control file fails, then your database becomes unavailable. As long as you have a control file copy, however, you can shut down your database and re-create the failed control file from the copy, then restart your database. Another option is to delete the failed control file from the CONTROL_FILES initialization parameter and restart your database with the remaining control files.

See Also:

Oracle Database Administrator's Guide for detailed information about control files

Displaying Control File Information

Click Control Files to open the Control Files general page. This page shows whether your database has a multiplexed control file. The Advanced and Record Section pages give you more detailed information about your control file. Explanations of this information are contained in the online Help.

Online Redo Log Files

Every Oracle database has a set of two or more online redo log files. The set of redo log files is collectively known as the redo log for the database. A redo log is made up of redo entries, which are also called redo records.

The redo log records all changes made to data. If a failure prevents modified data from being permanently written to the datafiles, then the changes can be obtained from the redo log, so work is never lost. To protect against a failure involving the redo log itself, Oracle multiplexes the redo log so that two or more identical copies of the online redo log can be maintained on different disks.

Your database's redo log consists of groups of redo log files. A group consists of a redo log file and its multiplexed copies. Each identical copy is said to be a member of that group. Each group is defined by a number, such as Group 1.

The database log writer process writes redo records from the memory buffer to a redo log group until the group fills up or a log switch operation is requested. The log writer then writes to the next group. The log writer performs this action in a circular fashion so that the oldest group is overwritten by the most recent redo records.

See Also:

Oracle Database Administrator's Guide for detailed information about redo logs

Displaying Redo Log File Information

Click Redo Log Groups to open the Redo Log Groups page. This page shows the attributes of the redo log groups for your database. A database typically consists of three groups. Note the status attribute for the redo log groups. The CURRENT status group is the one currently being written to disk by the log writer.

When a redo log group contains only one member, it is not multiplexed. Click the number of a redo log group to view information about the redo log group members.

Multiplexing the Redo Log

While small database environments might not need multiplexed redo log groups, consider adding multiplexing as your database grows in users and applications. Multiplexing provides better protection for data in the case of instance or media failure.

To multiplex your redo log, you must add members to each redo log group. It is not required that redo log groups be symmetrical, but Oracle recommends that your groups all have the same number of members. A database must have a minimum of two redo log groups.

To create a multiplexed redo log for your database:

  1. From the Storage section of the Administration page, click Redo Log Groups.

    The Redo Log Groups page appears.

  2. Select a group and click Edit.

    The Edit Redo Log Group page appears.

  3. In the Redo Log Members section, click Add.

    The Add Redo Log Member page appears.

  4. Enter the file name for the new redo log member. For example, if your existing member file name is REDO01.log, then you might name this member REDO01a.log.

    Enter the file directory or accept the default. You can create this file in the same directory, but it is recommended that you store members on separate drives. That way, if there is a drive failure, you still have access to one member.

  5. Click Continue, then Click Apply to accept your changes.

  6. Repeat these steps for every existing group.

To switch a log file:

  1. Navigate to the Redo Log Groups page.

  2. From the Actions menu, select Switch logfile.

  3. Click Go.

    You can see that the next groups status changes from INACTIVE to CURRENT.

Archive Log Files

When you archive your redo log, you write redo log files to another location prior to their being overwritten. This location is called the archive log. You can archive to multiple locations, including a standby database.

These copies of redo log files extend the amount of redo data that can be saved and used for recovery. Archiving can be either enabled or disabled for the database, but Oracle recommends that you enable archiving.

See Also:

Oracle Database Administrator's Guide for detailed information about archived redo logs

Displaying Archive Log File Information

Click Archive Logs to display the Archive Logs page. This page lets you view archive log files and their attributes.

For placing the database into archive log mode, or adding archive log locations, see "Configuring Your Database for Basic Backup and Recovery".

Rollback Segments

Rollback segments were database structures used to track undo information for the database in earlier releases of Oracle. Now, the preferred way of managing undo is with the undo tablespace. For more information, see "Managing Undo for Your Database" in this chapter.

Displaying Rollback Segment Information

Click Rollback Segments to display the Rollback Segments page. This page displays the attributes of the system rollback segment. The system rollback segment is self-managing.


Datafiles are the operating system files that hold the data within the database. The data is written to these files in an Oracle proprietary format that cannot be read by other programs. Tempfiles are a special class of datafiles that are associated only with temporary tablespaces.

Datafiles can be broken down into the following components:

Segments and Extents

A segment contains a specific type of database object. For example, tables are stored in data segments, whereas indexes are stored in index segments.

An extent is a contiguous set of data blocks within a segment. Oracle initially allocates an extent of a specified size for a segment, but if that extent fills, then more extents can be allocated.

Data Blocks

Data blocks, also called database blocks, are the smallest unit of I/O to database storage. An extent consists of several contiguous data blocks. The database uses a default block size at database creation.

After the database has been created, it is not possible to change the default block size without re-creating the database. Nevertheless, it is possible to create tablespace with a block size different than the default block size. For details on how to create tablespaces with a nondefault or nonstandard block size, see Oracle Database Administrator's Guide.

Viewing Datafile Information

Click Datafiles to open the Datafiles page. You can use this page to view information about your datafiles. Select a datafile and click View to display more attributes for the datafile. You can also click the name of the tablespace to display the attributes of the owning tablespace.

See Also:

Oracle Database Administrator's Guide for detailed information about datafiles


A database consists of one or more tablespaces. A tablespace is a logical structure, consisting of one or more datafiles or tempfiles.

Tablespaces hold data in the form of other objects such as tables and indexes. When you create an Oracle database, some tablespaces already exist, such as SYSTEM and USERS. For a complete list, see "Some Tablespaces in the Database" .

You can create new tablespaces to support your user and application data requirements. During tablespace creation, you set the following parameters:

Locally-Managed vs. Dictionary-Managed Tablespaces

Space management within a tablespace involves keeping track of free and used space, so that space is allocated efficiently during data insertion and deletion.

Oracle recommends creating locally-managed tablespaces over dictionary-managed tablespaces. Locally-managed tablespaces keep the space allocation information within the tablespace, not in the data-dictionary, thus offering better performance.

By default, Oracle sets all newly created tablespaces to be locally-managed with automatic segment management, a feature that further improves performance.

Tablespace Type

Tablespaces can be one of three types: permanent, undo, or temporary.

Permanent Tablespaces

Oracle uses these tablespaces to store permanent data, such as system data. You use permanent tablespaces to store your user and application data.

Undo Tablespace

A database running in automatic undo management mode transparently creates and manages undo data in the undo tablespace. Oracle uses undo data to provide read-consistency and to enable features such as Flashback query.

Even though you can create more than one undo tablespace, only one can be active. If you want to switch the undo tablespace used by the database instance, however, then you can create a new one and instruct the database to use it instead. The undo tablespace no longer in use can be dropped later.

Temporary Tablespaces

Temporary tablespaces are used for storing temporary data, as would be created when SQL statements perform sorts. After database creation, the database already has a temporary tablespace.

You would create another temporary tablespace if you were creating a temporary tablespace group. Under normal circumstances, you need not create additional temporary tablespaces.

Temporary and permanent tablespaces can be assigned at the user level. You can create a default temporary tablespace at database creation time. This tablespace is used as the default temporary tablespace for users who are not otherwise assigned a temporary tablespace.

See Also:

Oracle Database Administrator's Guide to learn more about temporary tablespaces

Tablespace Status

You can set tablespace read-write status and its availability as follows:

Read Write

Users can read and write to the tablespace after it is created. This is the default.

Read Only

If the tablespace is created read-only, then the tablespace cannot be written to until its status is changed to Read Write. It is unlikely that you create a read-only tablespace, but you might change it to that status after you have written data to it that you do not want modified.


If the tablespace is created offline, then no users can access it. It is unlikely that you create an offline tablespace, but you might change its status to offline later to perform maintenance on its underlying files.

Auto-Extend Tablespace

You can set a tablespace to automatically extend itself by a specified amount when it reaches its limit.

If you do not enable autoextend, you will be alerted when the tablespace reaches its critical or warning threshold size. The critical and warning threshold parameters have default values which you can change at any time. You can respond to space alerts by manually increasing the tablespace.

Some Tablespaces in the Database

Table 6-1 describes some of the tablespaces included in the database.

Table 6-1 Tablespaces and Descriptions

Tablespace Description


This tablespace contains the sample schemas that Oracle includes with the database. The sample schemas provide a common platform for examples. Oracle documentation and educational materials contain examples based on the sample schemas.


This is an auxiliary tablespace to the SYSTEM tablespace.

Some components and products that used the SYSTEM tablespace or their own tablespaces prior to Oracle Database 10g now use the SYSAUX tablespace. Using SYSAUX reduces the load on the SYSTEM tablespace and reduces maintenance because there are fewer tablespaces to monitor and maintain. Every Oracle Database 10g or higher level database must have a SYSAUX tablespace.

Components that use SYSAUX as their default tablespace during installation include Automatic Workload Repository, Oracle Streams, Oracle Text, and Enterprise Manager Repository. For more information, see the Oracle Database Administrator's Guide.


This tablespace is always created at database creation. Oracle uses it to manage the database. It contains the data dictionary, which is the central set of tables and views used as a read-only reference for a particular database. It also contains various tables and views that contain administrative information about the database. These are all contained in the SYS schema, and can only be accessed by user SYS or other administrative users with the required privilege.


This tablespace stores temporary data generated when processing SQL statements. For example, this tablespace would be used for query sorting. Every database should have a temporary tablespace that is assigned to users as their temporary tablespace. In the preconfigured database, the TEMP tablespace is specified as the default temporary tablespace. This means that if no temporary tablespace is specified when a user account is created, then Oracle assigns this tablespace to the user.


This is the undo tablespace used by the database server to store undo information. See "Managing Undo for Your Database" to understand how an Oracle database uses the undo tablespace. Every database must have an undo tablespace.


This tablespace is used to store permanent user objects and data. Like the TEMP tablespace, every database should have a tablespace for permanent user data that is assigned to users. Otherwise, user objects will be created in the SYSTEM tablespace, which is not good practice. In the preconfigured database, USERS is assigned the default tablespace, and space for all objects created by non-system users comes from this tablespace. For system users, the default permanent tablespace remains SYSTEM.

Other Storage Structures

Other storage structures that can exist in an Oracle database include the initialization parameter file, the password file, and backup files.

Initialization Parameter File

Initialization parameters are used by the database server at startup to determine the runtime resources for the database. They are actively monitored by the database and can be set or modified while the database is running.

Initialization parameters and the initialization parameter file are discussed in Chapter 5, "Managing the Oracle Instance".

Password File

A database can use a password file to authenticate administrative users with SYSDBA connect privileges. SYSDBA privileges enable a DBA to start up and shut down the database and perform other high-level administrative tasks. This password file is outside of the database itself because it must sometimes be referenced when the database is not yet running.

This is not the only form of administrator authentication, so not all databases require a password file.

The password file is discussed in "Accessing the Database" in Chapter 5, "Managing the Oracle Instance".

Backup Files

Backup files are not technically database files, but are copies of the database in some form that can be used to recover the database if a failure causes loss of data.

Backup files are discussed in Chapter 9, "Performing Backup and Recovery".

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

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.

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

    The Tablespaces page appears.

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

  3. Enter a name for the tablespace. For example, enter TB1.

  4. In the Extent Management section, select Locally Managed. See "Locally-Managed vs. Dictionary-Managed Tablespaces" .

  5. In the Type section, select Permanent. See "Tablespace Type".

  6. In the Status section, select Read Write from among the status options. See "Tablespace Status".

  7. In the Datafiles section of the page, click Add to add one or more datafiles to the tablespace. See "Datafiles".


    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.
  8. In the Add Datafiles page, enter a file name. For example, enter TB1.dbf. Accept the File Directory and File Size defaults.

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

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

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

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

    The Tablespaces page appears.

  2. Select a tablespace to autoextend, such as USERS, and click Edit.

    The Edit Tablespace: USERS General property page appears.

  3. Select the datafile associated with the tablespace and click Edit.

    The Edit Datafile page appears.

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

  5. Click Continue to return to the Edit Tablespace page.

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

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

    The Tablespaces page appears.

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

  3. 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 follows
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:

  1. 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 follows
    Description of "Figure 6-3 Segment Advisor Recommendations"

  2. 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 follows
    Description of "Figure 6-4 Segment Advisor Recommendation Details"

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

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

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

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

See Also:

Oracle Database Administrator's Guide for more information on running the Segment Advisor

Managing Undo for Your Database

This section discusses undo management. These tasks involve storing the changes of database transactions long enough to accommodate rollback, read consistency, and flashback features.

After you install the database, you can start building your database without immediately managing undo because the database automatically performs this task. Later, as your database activity and transaction rate increase, understanding how to manage undo becomes more useful to you.

This section contains the following topics:

About Undo Data

When a transaction modifies the database, Oracle copies the original data before modifying it. The original copy of the modified data is called undo data. Saving this information is necessary for the following reasons:

  • To undo any uncommitted changes made to the database in the event that a rollback operation is necessary. A rollback operation can be the result of a user who wants to undo the changes of a misguided or unintentional transaction, or it can be part of a recovery operation.

  • To provide read consistency, which means that each user can get a consistent view of data, even while other uncommitted changes may be occurring against the data. For example, if a user issues a query at 10:00 a.m. and the query lasts for 15 minutes, then the query results should reflect the entire state of the data at 10:00 a.m., regardless of updates or inserts by other users during the query.

  • To enable certain Flashback features, namely Flashback Query and Flashback Table, which enable you to view or recover data to a previous point in time.

Undo Tablespace Size and Retention Time

Undo data is stored in a logical database structure called an undo tablespace. The undo tablespace is of finite size, so records might be overwritten as transactions occur.

Oracle saves undo data at least until the transaction has been committed. Until this time, the undo data is in the active state. Therefore, the amount of space available in the undo tablespace should be at least large enough to hold the active undo data generated by current transactions. Otherwise, some of these transactions might fail. When active undo data is stored in the undo tablespace, Oracle automatically ensures that it is never overwritten until the corresponding transaction has been committed.

Even after the transaction has been committed, the undo data still cannot be overwritten immediately to ensure the success of Flashback functionality, and for read consistency for long running transactions. For example, if your longest query takes 15 minutes, then the undo tablespace should be large enough to hold 15 minutes worth of undo data.

To control the retention of undo records, Oracle maintains an undo retention period. This period indicates the amount of time that must pass before Oracle overwrites undo data. The undo retention period affects the size of the undo tablespace; the longer the retention period, the more space is needed.

The undo retention period should be at least as long as your longest-running query. By default, Oracle automatically extends the undo tablespace to accommodate the longest-running query based on your ongoing system activity. Nevertheless, you might need to manually increase the size of your undo tablespace in the following circumstances:

  • When your undo tablespace is set to a fixed size (auto-extend disabled) and long running queries are failing with snapshot too old errors.

  • When you plan to use Flashback features to recover from user errors such as unintentional changes. In this case, the undo retention should be set equal to the period between the present and the earliest point in time to which you want to return. For more details on Flashback features, see Oracle Database Administrator's Guide.

Automatic Undo Management

The Oracle database automatically determines how long undo data should be kept based on the time your queries take to run. Undo data preserved within this window of time is said to be in the unexpired state. After this time, the state of the undo data changes to expired. Undo data is a good candidate for overwriting only when it is in the expired state.

The length of time that Oracle keeps undo data in the unexpired state depends on your tablespace configuration. When you create your database with DBCA, the undo tablespace is set by default to automatically extend itself to maintain unexpired undo for the longest-running query.

With a fixed-sized undo tablespace, Oracle automatically keeps the undo data in the unexpired state for the longest possible time for the tablespace of the specified size. If the undo tablespace does not have adequate free or expired space to store active undo data generated by current transactions, however, then Oracle might be forced to overwrite the unexpired undo data. This situation might cause long-running queries to fail with an error and an alert.

To avoid situations in which long-running queries can fail, it is recommended that you let Oracle automatically extend the size of the undo tablespace. By default, the undo tablespace is set to auto-extend when you use DBCA to configure your database.

You may choose to disable auto-extension and adjust the size of the tablespace manually. In this case, ensure that the tablespace is large enough to meet the read-consistency requirements for your longest-running query. Also, if you use Flashback features, then the tablespace must be large enough to accommodate Flashback operations. Oracle Enterprise Manager includes an Undo Advisor to help you determine the optimal size. See "Using the Undo Advisor".

Managing Undo with Enterprise Manager

With Enterprise Manager, you can manage undo as follows:

  1. From the Database Control home page, click Administration.

    The Administration property page appears.

  2. In the Database Configuration section, click Undo Management.

    The Undo Management page appears, as shown in Figure 6-5.

Figure 6-5 Undo Management page

Description of Figure 6-5 follows
Description of "Figure 6-5 Undo Management page"

You can use the Undo Management page to view the following about your undo configuration:

  • Name and size of undo tablespace

  • Auto-extend tablespace setting

  • Auto-tuned undo retention period

  • Minimum retention period

In Undo Tablespace for this Instance, the Auto-Extensible field shows Yes if auto-extending the tablespace is enabled, which is the default. When the undo tablespace is auto-extensible, Oracle automatically increases the size of the tablespace when more space is needed. By combining automatic extension of the undo tablespace with automatically tuned undo retention, you can ensure that long-running queries succeed by guaranteeing the undo for such queries.

If you have a fixed-size tablespace, then you can use this page as a starting point for determining space requirements and extending the tablespace. See "Using the Undo Advisor".

The Undo Retention Settings section describes your minimum undo retention period. While Oracle automatically tunes the undo retention period, minimum undo retention enables you to define the lowest value allowable for your database. When you create a database, the minimum undo retention is set to a default value. You might need to alter this value to build a recovery strategy using Flashback Query. See "Setting Minimum Undo Retention Time".

The Recommendations section of the Undo Management page gives you recommendations on undo tablespace size based on your system activity. Recommendations and alerts can arise when you are using a fixed-size tablespace and queries have been failing because of insufficient undo space. On rarer occasions, when using an auto-extend tablespace, the same recommendations can arise if the system has reached it maximum disk limit.

If Oracle recommends that you extend the undo tablespace, then you can use the Undo Advisor to determine a better size.

Using the Undo Advisor

The amount of undo data that can be retained depends on the size of your undo tablespace. If your tablespace is set to auto-extend, then Oracle automatically acquires space as needed. If you choose to disable auto-extend, however, then you are responsible for ensuring that the undo tablespace has enough space. In this situation, Oracle configures the undo retention to the maximum possible value for that tablespace size.

The Undo Advisor helps you analyze various scenarios to determine an appropriate undo tablespace size for different values of maximum undo retention. This analysis might be required in the following situations:

  • You have a fixed-sized tablespace (auto-extend disabled) and the auto-tuned value of undo retention is not large enough to prevent queries from failing. The Undo Advisor can help you determine a better tablespace size to ensure successful completion of your queries. See "Gaining Advice from Undo Advisor".

  • You use Flashback features such as Flashback Query or Flashback Table. For Flashback operations to go back in time, the database must ensure that undo data is not overwritten.

    To build a flashback recovery strategy, you can set the minimum undo retention, which determines the lowest value for automatic undo tuning. For example, if the low threshold is set to 15 minutes, then Oracle never lowers the undo retention time to less than 15 minutes. Consequently, if your flashback recovery strategy requires you to go back 8 hours to recover from human errors, then set the minimum undo retention to 8 hours. To learn how to set a new retention time, see "Setting Minimum Undo Retention Time"

Gaining Advice from Undo Advisor

The Undo Advisor can help you determine a better size for your undo tablespace to ensure successful completion of queries. Follow these general steps to determine the required new size of your tablespace:

  1. Determine the duration of your longest running query according to your application characteristics. If this duration is longer than the tuned retention, then these long-running queries will encounter an error. In this case, your undo tablespace is too small. You need to either set your tablespace to auto-extend or manually extend it.

  2. In the Database Configuration section of the Administration page, click Undo Management.

    The Undo Management page appears, as shown in Figure 6-5.

  3. Click Undo Advisor.

    The Undo Advisor page appears. The top of the page shows the current auto-tuned undo retention time and undo tablespace size, as shown in Figure 6-6.

  4. In the New Undo Retention field of the Advisor section, plug in the value of your longest-running query.

  5. In the Analysis Time Period list, select a time period that best reflects your business cycle.

    The Analysis section displays the Required Tablespace size for New Undo Retention.

For example, suppose we have a fixed size tablespace of 25 MB. We determine the longest running query is 60 minutes. We enter this number in the New Undo Retention field as shown in Figure 6-6. The advisor recommends 10 MB of space to accommodate this query. Because our tablespace is 25 MB, our undo tablespace is adequately configured.

Figure 6-6 Undo Advisor with Fixed-Sized Tablespace

Description of Figure 6-6 follows
Description of "Figure 6-6 Undo Advisor with Fixed-Sized Tablespace"

Additionally, the Required Tablespace Size by Undo Retention Length graph shows the relationship between retention period and undo tablespace size, highlighting key data points such as the Auto-tuned Undo Retention and Best Possible Undo Retention.

Extending the Undo Tablespace

When auto-extend tablespace is enabled, the system automatically extends the undo tablespace if it is under space pressure. When the auto-extend tablespace feature is disabled, however, you might need to manually extend the undo tablespace. You might discover this information ahead of time while planning with the Undo Advisor. See "Using the Undo Advisor".

You might also need to extend the undo tablespace when you get an undo tablespace alert (warning or critical), or when you get a query too long alert or snapshot too old error.

To resize the tablespace:

  1. From the Undo Management page, click Edit Undo Tablespace.

    The Edit Tablespace page appears.

  2. Select a datafile to extend and click Edit.

    The Edit Datafile page appears.

  3. In the File Size field, enter a new datafile size.

    You can also have the system automatically extend the datafile by enabling Automatically extend datafile when full under Storage and specifying an increment size.

  4. Click Continue.

    The Edit Tablespace page appears.

  5. Under Datafiles, Click Apply.

    A confirmation message should appear.

Setting Minimum Undo Retention Time

If you are using the Flashback Query or Flashback Table feature and need to configure a flashback recovery strategy to go back in time, then configure the minimum undo retention as follows:

  1. In the New Undo Retention field of the Undo Advisor page, enter the new retention period.

    For configuring Flashback Query and Flashback Table, set a value equal to how far back in time you need to go. For example, if you need an eight hour flashback recovery strategy, set the minimum retention to 8 hours.

  2. Click OK.

Alternatively, you can set this parameter as you do for other system parameters by navigating to the All Initialization Parameters page. Set the parameter called undo_retention. For more information about this page, see "Viewing and Modifying Initialization Parameters".

Making Changes to the Database Storage Structure

The preconfigured database that you installed includes all of the database structures of a basic database. As the user base grows, you can expand existing database storage structures or create additional ones. For example, you might need to create additional tablespaces for users or applications, or you might want to create additional online redo log groups to expand the redo log capacity.

Oracle provides alerts, advisories, and monitoring pages to help you make decisions regarding database storage. These topics are discussed in Chapter 10, "Monitoring and Tuning the Database". Oracle lets you create your own alerts for other events that you want to monitor.

The Storage section links shown in "Storage Options" are links that you can use to make changes to the database storage structure. Other pages also contain links that enable you to perform specific actions that affect the database storage structure.

For example, the Maintenance page includes a Backup/Recovery Settings section. This section includes a link for changing the archiving mode of the database and creating archived redo log files (see "Configuring Your Database for Basic Backup and Recovery").

When you make changes to the storage structure of your database, these changes can be reflected in the data dictionary and in the control file. It is important to consider this fact in your backup and recovery strategy. This topic is discussed in Chapter 9, "Performing Backup and Recovery".

Storage: Oracle by Example Series

Oracle by Example (OBE) has a series on the Oracle Database 2 Day DBA book. This OBE steps you through the tasks in this chapter and includes annotated screen shots.

To view the Storage OBE, point your browser to the following location: