Skip Headers

Oracle® Database 2 Day DBA
10g Release 1 (10.1)

Part Number B10742-03
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page
Go to next page
View PDF

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 comprised of physical and logical structures, which you, as a DBA, are responsible for managing. Physical structures are those that can be seen and operated upon 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 correlation between the physical and logical structures of a database and control the mapping of the logical structure of the database to the physical structure.

Oracle has functionality within the database server to automate the management of its structure. Further, Oracle Enterprise Manager provides a Web-based graphical user interface to enable easy management and monitoring of your database.

You view database storage structure by clicking the links shown under the Storage heading on the Administration Home page (see Figure 5-2, "Database Administration Page"). Figure 6-1 shows these links.

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

Control File

A control file tracks the physical components of the database and other control information. It is essential to the functioning of the database. Because of this, Oracle recommends that the control file be multiplexed. In other words, it should have multiple identical copies. For databases created using DBCA, three copies of the control file are automatically created.

If any control file fails, then your database becomes unavailable. But as long as you still have any of the control file copies intact, you can shut down your database and re-create the failed control file from one of the other control files, 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. There are other options, depending upon circumstances. See Oracle Database Administrator's Guide for detailed information about control files.

Click the Controlfiles link. The Controlfiles General page shows that your database has a multiplexed control file. There are two other property pages, in addition to the General page, for viewing control file information. 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.

Redo Log Files

Every Oracle database has a set of two or more 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 (also called redo records). The primary function of the redo log is to record 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 allows a multiplexed redo log so that two or more copies of the 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, and 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, then it writes to the next group. This is done in a circular fashion, where the oldest group is overwritten by the most recent redo records.

Click Redo Log Groups. The Redo Log Groups page appears, showing 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 process.

You can see that each group contains only one member. This is because the database does not have a multiplexed redo log. Select a redo log group and click View to see information about the redo log group members.

Multiplexing the Redo Log

While not multiplexing redo log groups is acceptable in a small database environment, you should consider adding multiplexing as your database grows in users and applications. This 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. This example will add one member to each redo log group. It is not required that redo log groups be symmetrical (all have the same number of members), but in practice it is recommended that this be the case. It is required that a database have a minimum of two redo log groups.

To create a multiplexed redo log for your database, follow these steps:

  1. Navigate to the Redo Log Groups page.

  2. Select a group and click Edit, or click the group number link.

    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 example, if your existing member file name is REDO01, you might name this member REDO01a. Enter the file directory. Click OK.

    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. Repeat these steps for every existing group.

When you add the redo log member to a group, the group's status is marked INVALID. This is a normal state because there is a member that has not been written to yet. When a log switch occurs, and the invalid group becomes the current group, then its status is marked CURRENT.

To see the status change on a log switch:

  1. Navigate to the Redo Log Groups page.

  2. From the Actions pull down menu, select Switch logfile. Click Go.

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

Archive Log Files

When you archive your redo log, you write redo log files to another medium 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 information that can be saved and used for recovery. Archiving can be either enabled or disabled for the database, but Oracle recommends you enable archiving.The status of a log group that is actively being archived is ACTIVE.

Click Archive Logs. The Archive Logs page is displayed. 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

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

Click Rollback Segments. You see the attributes of the system rollback segment. The system rollback segment is self managing.


A database consists of one or more tablespaces. A tablespace is a logical structure, or container, created by and known only to the Oracle database server in which database objects are stored. A tablespace consists of one or more datafiles or tempfiles.

There are various types of tablespaces, including undo tablespaces, temporary tablespaces, and permanent tablespaces.

A database running in automatic undo management mode transparently creates and manages undo segments. You can create an undo tablespace and determine the maximum retention time for undo data kept in that tablespace.

Temporary tablespaces improve the concurrence of multiple sort operations, reduce their overhead, or avoid Oracle space management operations altogether. Temporary tablespaces are the most efficient tablespaces for disk sorts. Space management (extent allocation and deallocation) is locally managed. After temporary tablespaces, tablespaces of type TEMPORARY are the next best tablespaces to use for sort operations. Space management is dictionary managed. Permanent tablespaces (which are not of type TEMPORARY) are least efficient for performance of disk sorts.

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.

Tablespaces Page

Click Tablespaces. The Tablespaces page provides high level information about tablespace structure and space usage. You can see how much space is allocated for the tablespace and what portion of it is used. Select a tablespace and click View to see the underlying datafile attributes.

Some Tablespaces in the Database

Table 6-1 provides a description some of the tablespaces included in the database.

Table 6-1 Tablespaces and Descriptions

Tablespace Description
SYSAUX This is an auxiliary tablespace to the SYSTEM tablespace.

Some components and products that prior to Oracle Database 10g used the SYSTEM tablespace or their own tablespaces now use the SYSAUX tablespace. This 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 this 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.

SYSTEM The SYSTEM 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.
TEMP This tablespace stores temporary data generated when processing SQL statements. It would, for example, be used for sort work space. 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.
UNDOTBS1 This is the undo tablespace used by the database server to store undo information. See "Managing Undo for Your Database" to understand Oracle's use of the undo tablespace. Every database must have an undo tablespace that can be created at database creation.
EXAMPLE 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.
USERS 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, their objects will be created in the SYSTEM tablespace, and this 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.

Temporary Tablespace Groups

Temporary tablespace groups let users consume temporary space from multiple tablespaces. Using a tablespace group, rather than a single temporary tablespace, can alleviate problems caused when one tablespace is inadequate to hold the results of a sort, particularly on a table that has many partitions. A tablespace group enables parallel execution servers in a single parallel operation to use multiple temporary tablespaces.

You can assign a tablespace group name as a default temporary tablespace for the database or as a temporary tablespace for a user.

Click the Temporary Tablespace Groups link. You see the temporary tablespace groups that are defined for your database. Initially, there are none.

See Also:

Oracle Database Administrator's Guide for details on using tablespace groups


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 programs other than the database server. 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, and 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 block

    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 default database block size is specified by a DBA at database creation or defaulted by Oracle.

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

To view more information about your database datafiles, click Datafiles. Your database datafiles are displayed. Select a datafile and click View to see more attributes for the datafile, or select a tablespace link to see the attributes of the owning tablespace.

Other Storage Structures

Other storage structures that can exist in an Oracle database include the following:

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 might use a password file to authenticate administrative users who can have SYSDBA connect privileges. SYSDBA connect 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 technically not database files, but rather copies of the database in some form that can be used to recover the database should a failure causing loss of data occur.

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

Common DBA Tasks for Managing Database Storage

The remainder of this chapter presents examples of some common database administration tasks relating to the storage structure:

Viewing Tablespaces in Your Database

You can see all tablespaces in your database. From the Administration page, click Tablespaces under Storage. This takes you to the Tablespaces page.

The Results page shows all tablespaces, including the following information about each: type of tablespace, type of extent management, type of segment management, status, size, and percent used. Click the tablespace name for more information.

Creating a Tablespace

A particular user, groups of users, or an application may 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. This exercise will also familiarize you with the various attributes that you can set, and possibly later modify, for a tablespace.

  1. From the Administration page, click Tablespaces under Storage. This takes you to the Tablespaces page.

  2. Click Create. Or, if you want to create a tablespace that is like an existing tablespace, select an existing tablespace. Then from the Actions menu, select Create Like.

    The Create Tablespace General page appears.

    Did you notice the other options that are available from the Actions pull down menu? These are shortcuts for various tablespace operations.

  3. Enter a name for the tablespace. For example: TB1

  4. Under the Extent Management heading, select Locally Managed.

    A locally managed tablespace's extents are managed locally and efficiently within the tablespace by Oracle. For a dictionary managed tablespace, you must more actively manage extents and data dictionary access is required for tracking them. Dictionary managed tablespaces are being deprecated. Oracle does not recommend their use.

  5. Under the Type heading, select appropriate type.

    Permanent tablespaces store permanent database objects created by the system or users.

    Temporary tablespaces are for storing temporary segments, as would be created when SQL statements perform sorts. The preconfigured database already has a temporary tablespace. You would create another temporary tablespace if you were creating a temporary tablespace group. Under normal circumstances, you should not need to create additional temporary tablespaces.

    Undo tablespaces are used by the system to store undo information. You might want to create another undo tablespace, but only one undo tablespace can be the active one.

    Again, you should not need to create additional undo tablespaces. However, if you want to switch the undo tablespace used by the database instance, then you can create a new undo tablespace and instruct the database to use it instead of the undo tablespace currently in use. The undo tablespace no longer in use can be dropped later.

  6. Under Status, select Read Write.

    Read Write status means users can read and write to the tablespace after it is created. This is the default.

    If the tablespace is created read-only, then the tablespace cannot be written to until its status is changed to Read Write. You would probably not create a tablespace as read-only, but rather 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 have access to it. You would probably not create a tablespace as offline, but rather you would change its status to offline to perform some maintenance on its underlying files.

  7. In the Datafiles section of the page, you can add one or more datafiles to the tablespace. However, if you select Use bigfile tablespace, then the tablespace can have only one datafile. Click Add.

    Bigfile tablespaces are used with very large databases using Oracle's Automatic Storage Management or other logical volume managers support striping or RAID, and dynamically extensible logical volumes.

  8. In the Add Datafiles page, enter a file name. For example: TS101.dbf. Use the File Directory and File Size defaults.

    At least one datafile is required for the SYSTEM and SYSAUX tablespaces of a database. Your database should contain several other tablespaces with their associated datafiles or tempfiles. The number of datafiles that you anticipate creating for your database can affect the settings of initialization parameters and the specification of CREATE DATABASE statement clauses. Be aware that your operating system might impose limits on the number of datafiles contained in your Oracle database. Also consider that the number of datafiles, and how and where they are allocated can affect the performance of your database.

  9. To allow Oracle to automatically grow the datafile, under Storage, select Automatically extend datafile when full (AUTOEXTEND) and 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.

    You are returned to the Create Tablespace General page.

  10. Click the Storage property page.

    The Create Tablespace Storage page appears.

    You can manually resize a datafile with the ALTER DATABASE statement. This lets you add more space to your database without adding more datafiles, if you are concerned about reaching the maximum number of datafiles allowed in your database. For a bigfile tablespace, use the ALTER TABLESPACE statement. You cannot add a datafile to a bigfile tablespace. Manually reducing the sizes of datafiles lets you reclaim unused space in the database. This is useful for correcting errors in estimates of space requirements.

  11. Leave all of the defaults set on the Storage page. See online Help for more information on these settings.

  12. Click the Thresholds property page.

    The Thresholds page appears. 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.

  13. After determining thresholds, click OK to add the tablespace. The Tablespaces page appears where you can see your new tablespace in the Results section.

Modifying a Tablespace

With Enterprise Manager, you can modify a tablespace, such as extend it, set it to automatically extend, change its space thresholds, or change its status to offline. A tablespace might need extending when you get a critical and warning alert. You might need to offline a tablespace to perform recovery operations on it.

This section shows you how to set a tablespace to automatically extend when it reaches its limit. To enable autoextend tablespace, do the following:

  1. From the Administration page, click Tablespaces under Storage. This takes you to the Tablespaces page.

  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 M. You can optionally set a maximum file size or set file size to unlimited depending on your resources.

    You can use SQL statements to add datafiles and associate them with a tablespace. You can either specify the file specifications for the datafiles being created, or you can use the Oracle Managed Files feature to create files that are managed by the database server.

    You can also use SQL statements to manually resize a datafile. This lets you add space to your database without adding datafiles, if you are concerned about reaching the maximum number of datafiles allowed in your database. Manually reducing the sizes of datafiles lets you reclaim unused space in the database. This is useful for correcting errors in estimates of space requirements. For more information on managing datafiles, see the Oracle Database Administrator's Guide.

  5. Click Continue. This brings you back 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 navigating to the Threshold property page. Here you can alter your space thresholds that generate the warning and critical alerts. For more information about thresholds and alerts, see Chapter 10, "Monitoring and Tuning the Database".

To take a tablespace offline, on the Edit Tablespace page, under Status, select Offline and click Apply.

Dropping a Tablespace

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

  1. From the Administration page, click Tablespaces under Storage. This takes you to the Tablespaces page.

  2. Select the tablespace that you want to drop, and click Delete. Enterprise Manager will ask for confirmation that you want to delete the tablespace and associated datafiles from the operating system.

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.

Oracle lets you reclaim wasted space through a shrink operation. In a shrink operation, data is compacted to the front of the table. Free space can either be given back 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.

Using the Segment Advisor

The Segment Advisor performs growth trend analysis on individual objects to determine if there will be any additional space left in the object in 7 days. It then uses the reclaim space target to select candidate objects to shrink. The Segment Advisor can be invoked in the comprehensive mode. In this mode, in addition to using the pre-computed statistics in the workload repository, the Segment Advisor samples the objects under consideration to refine the statistics for the objects. Although this operation is more resource intensive, it may be used to perform a more accurate analysis, when desired.

You can run the advisor at the tablespace or object level. To run the advisor from the tablespace level do the following:

  1. From the Administration page under Storage, click Tablespaces. This takes you to the Tablespaces page.

  2. On the Tablespaces page, select a tablespace and select Run Segment Advisor from the Actions menu. Note the used space before the operation for later comparison. Click Go. Note that the Segment Advisor can also be launched from the Advisor Central page.

  3. The Segment Advisor: Advisor Mode page appears. On this page, accept the default Complete Analysis of All Segments (Comprehensive) mode. Click Continue.

  4. The Segment Advisor: Options page appears. Accept Unlimited as the time limit for Analysis. Click Next.

  5. The Segment Advisor: Schedule page appears. Select Standard as the Schedule Type and accept Immediately as the Start time. Otherwise, you can schedule the task to run later. Click Next.

  6. The Segment Advisor: Review page appears summarizing the task and selected objects. Verify that the task will run as you want, and click Submit.

  7. The Advisor Central page appears listing the advisor related tasks that have run. Select the Segment Advisor task that you submitted, and click View Result.

  8. The Segment Advisor Task page appears showing the recommendations for the selected table. To implement one or more recommendations, select the recommendations to implement. This will launch the shrink wizard, which is described in the following section.

Shrinking Database Objects

Over time, it is common for segment space to become fragmented or for a segment to acquire a lot of free space as the result of update and delete operations. The resulting sparsely populated objects can suffer performance degradation during queries and DML operations.The Segment Advisor provides advice on whether an object has space available for reclamation based on the level of space fragmentation within an object.

  1. Select Shrink Options at the bottom of the page. You can choose to Compact Segments and Release Space, which releases freed space to the tablespace, or you can Compact Segments only. Click Schedule Implementation to run the shrink operation.

  2. The Schedule Implementation page appears. Accept a start time of Immediately, and click Submit.

  3. The scheduler Jobs page appears showing the status of the job. Click Refresh to update the page until the status of the job is Completed. On successful completion, you can navigate back to the Tablespace page and compare the current used space with the previous.

Managing Undo for Your Database

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

When you install the database, Oracle automatically enables auto-tuning of the undo retention period. (This parameter is discussed in "Undo Tablespace Size and Retention Time" .) Auto-tuning of undo retention enables you to start building your database without immediately managing undo. Later, as your database activity and transaction rate increase, understanding how to manage undo will become more useful to you.

About Undo

Undo data stores changes made to the database by transactions. Undo is necessary for the following:

  • 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 specifically issuing a rollback statement to undo the changes of a misguided or unintentional transaction, or it can be part of a recovery operation.

  • To provide read consistency. This 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, the query results should reflect the entire state of the data at 10:00 a.m. regardless of any updates or inserts by other users during the 15 minute query.

  • To enable Oracle's flashback features, which enables you to view or recover data to a previous point in time. These features include Flashback Query and Flashback Table.

Undo Tablespace Size and Retention Time

In order for changes to be rolled back or undone, Oracle makes a copy of the original data before modifying it. The original copy of the modified data is called undo data. Undo data is stored in a logical database structure called an undo tablespace.

The undo tablespace is of a finite size. Space usage within the tablespace is such that records can be overwritten. The undo data needs to be saved at least until the transaction has been committed. Until then the undo data is said to be in the active state. The amount of space available in the undo tablespace should, therefore, be at least large enough to hold the active undo data generated by currently active transactions. Otherwise some of these transactions may fail. After the 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. It is important to the success of flashback functionality, and for read consistency for long running transactions, that records not be overwritten too soon. For example, if your longest query takes 15 minutes, the undo tablespace should be big enough to hold 15 minutes worth of undo information.

To control the retention of undo records, Oracle maintains an undo retention period, which in turn affects the size of the undo tablespace; the longer the retention period, the bigger the tablespace. The undo retention period should be at least as long as your longest running query. In addition, if you plan to use flashback features to recover from human errors such as unintentional changes, the undo retention should be set equal to the amount of time in the past you want to go back. For more details on Flashback features, see Oracle Database Administrator's Guide.

Automatic Undo Management

Oracle Database automatically determines how long undo data should be preserved after the transaction has been committed based on queries running against the database. During this time, the undo data is said to be in the unexpired state. Oracle will keep the undo data in the unexpired state as long as needed by queries currently running against the database or as long as any low undo retention threshold (whichever is longer). After this time, the state of undo data changes to expired. Undo data is a good candidate for overwriting only when it is in the expired state.

If the undo tablespace does not have adequate free or expired space to store active undo data generated by current transactions, Oracle may be forced to overwrite the unexpired undo data. As this may cause your queries to fail, this is not a desirable situation. In order to avoid this, Oracle recommends you allow the size of the undo tablespace to be automatically increased by enabling auto-extend. See "Extending the Undo Tablespace".

If you cannot enable auto extension for any reason and choose to adjust the size of the undo tablespace manually, Oracle Database includes an undo advisor to help you determine the optimal size. See "Using the Undo Advisor".

Undo Management with Enterprise Manager

With Enterprise Manager, you can access the Undo Management page from the database Administration page by selecting Undo Management under the Instance heading.

You can use the Undo Management page to view undo configuration, including the auto-tuned undo retention period, the low threshold retention period, and the name and size of the undo tablespace. Auto-tuning of undo retention cannot be disabled.

You can also use this page to set the low threshold retention or to extend your tablespace. While Oracle automatically tunes the undo retention period, low threshold undo retention lets you define the floor value under which Oracle should never bring down the undo retention. When you create a database, the low threshold undo retention is set to a default value. You may need to alter this value only when you need to build a recovery strategy using Flashback Query.

To change the low threshold, click its value. The Undo Advisor page appears, which can help you determine a better setting. See "Using the Undo Advisor" for more information.

The Configuration section also shows if auto-extending the tablespace is enabled, which is the default. When auto-extend tablespace is enabled, Oracle automatically increases the size of the undo 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 will succeed by guaranteeing the undo required for such queries.

The methods for determining the undo retention period are described in the following sections:

See Also:

Oracle Database Administrator's Guide for more information about Automatic Undo Management and undo tablespaces.

Using Oracle Recommendations for Managing Undo

In the Recommendations section of the Undo Management page, you can specify a past time period for Oracle to analyze system activity. The current recommendations for your undo configuration are summarized. To update the analysis, click Update Analysis.

If Oracle recommends that you extend the undo tablespace, you can do so. See "Extending the Undo Tablespace" for more information.

To obtain more detailed information about the recommendations, click Undo Advisor. See also "Using the Undo Advisor" .

Extending the Undo Tablespace

When auto-extend tablespace is enabled, the system will automatically extend the undo tablespace if it is under space pressure.

However, when the auto-extend tablespace feature is disabled, you might need to manually extend the undo tablespace. You might discover this 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 "query too long" alert or "snapshot too old" error.

To resize the tablespace, click Edit Undo Tablespace. The Edit Tablespace page appears. To extend the tablespace, select a datafile and click Edit. Enter the new size in the File Size field. You can also opt to have the system automatically extend the datafile by enabling Automatically extend datafile when full under Storage and specifying an increment size.

Click Continue. Click Apply. A confirmation message should appear.

Using the Undo Advisor

You can use the undo advisor to advise on the undo tablespace size and the low threshold undo retention setting. To navigate to the Undo Advisor page, navigate to the Undo Management page and do one of the following:

  • Click Undo Advisor.

  • Click the value adjacent to Low Threshold Undo Retention.

The top of the page shows the current auto-tuned undo retention time and undo tablespace size.

The Undo Advisor helps you answer the following questions about your undo configuration:

  1. What is the maximum undo retention time required to guarantee successful query? This time period corresponds to the duration of your longest running query. You can find Longest Running Query on the Undo Management page under System Activity and Tablespace Usage.

  2. Does my database have enough space to accommodate the maximum undo retention given the current tablespace configuration? To figure this out, note the Best Possible Undo Retention under analysis on the Undo Advisor page. If this time period is longer than your longest running query, your undo tablespace is adequately configured.

  3. What if the best possible retention time is less than my longest running query? If this is the case, your undo tablespace is too small. You need to either set your tablespace to auto-extend or manually extend it. To figure out the required new size of your tablespace, on the Undo Advisor page New Undo Retention field, plug in the value of your longest running query. Enter an appropriate Analysis Time Period and click Update Analysis and Graph. Under Analysis, note the Required Tablespace size for New Undo Retention. To learn how to alter your tablespace, "Extending the Undo Tablespace".

  4. How do I configure undo to accommodate Flashback operations? If you are interested in using flashback features such as Flashback Query or Flashback Table, you can use the Undo Advisor to advise on how to configure your tablespace and undo retention. In order 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 low threshold parameter, which determines the lowest value for automatic undo tuning. For example, if the low threshold is set to 15 minutes, 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, set the low threshold to 8 hours. To learn how to set a new retention time, see "Setting New Threshold Undo Retention Time".

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 and Best Possible Retention.

Setting New Threshold Undo Retention Time

To set a new low threshold retention time from the Undo Advisor page, enter the value in New Undo Retention and click OK.

Alternatively, you can set this parameter as you do for other system parameters by navigating to the All Initialization Parameters page. The parameter is called undo_retention. For more information about this page, see "Viewing and Modifying Initialization Parameters" in Chapter 5, "Managing the Oracle Instance".

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 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 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 heading links shown in "Storage Options" are links that you can use to make changes to the database storage structure. Other pages also contain links, or drill down functionality, that enable you to perform specific actions that affect the database storage structure.

For example, there is a link for changing the archive log mode of the database and creating archive log files from the Recovery links on the database Maintenance page (see "Configuring Your Database for Basic Backup and Recovery"). Another example is changing the size of the undo tablespace, where you can use the Undo Advisor page to drill down to a page for changing the undo tablespace size (see "Managing Undo for Your Database").

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 both before and after making changes to the database's storage structure that you back up the database. To help you with this, Oracle has provided a backup strategy that you can use. This 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: