0478-0480

Previous Table of Contents Next

Page 478

Using Rollback Segments

Another type of segment that uses storage on the database is the rollback segment, which is a system object created by the database administrator. The rollback segment serves two purposes. First, for a transaction that makes changes, the old values of the changed data are kept in the rollback segment, which enables you to reverse them if the rollback statement is issued instead of being committed. Second, the rollback segment provides a read-consistent view of the data. This means that even if you change the data in a table and lock the rows, other users still can access the data. Users see the old values of any rows until your transaction commits.

An Oracle database always has a rollback segment with the name SYSTEM and the segment type ROLLBACK. You should create additional rollback segments so that the SYSTEM rollback segment is reserved for recording the rollback information when changes are made to the data dictionary system tables ”usually with DDL statements or by means of system-generated recursive SQL statements. To determine the number and sizes of the required rollback segments, look at the dynamic performance tables.

TIP

As a rule of thumb, one rollback segment should exist for every five users who are likely to make changes at any one time. Between 10 and 20 extents should exist for each rollback segment.

Creating Public and Private Rollback Segments

You can create two types of rollback segments: private and public. Private rollback segments are more common and are the default type. A private rollback segment can be used only to record rollback information for transactions running against the instance to which the rollback segment has been associated. A public rollback segment can be used to record information for any transaction running against any instance, which assumes that the parallel server version of the software is used and that many instances are running against the same database.

Creating Additional Rollback Segments

Creating an additional rollback segment involves two steps. You first must create it, and then you must activate it. To create a rollback segment, use the following command:

 SQLPLUS> CREATE ROLLBACK SEGMENT benisha; 

You can specify the storage parameter and the tablespace at the end of the CREATE statement in the same way you specified the parameters for a table. After you create the rollback segment, however, you must enable it.

Page 479

A rollback segment must have a minimum of two extents when it is created. Other database segments need to have a minimum of only one extent. In fact, rollback segments usually are created with many extents ”by using the MINEXTENTS parameter in the storage clause ”because of how they are used. Each transaction making a change uses only one extent of the rollback segment instead of the entire segment.

Enabling and Disabling Rollback Segments

You can enable a rollback segment in two ways. To activate a rollback segment immediately, issue the following command:

 SQLPLUS> ALTER ROLLBACK SEGMENT benisha ONLINE; 

This command activates the rollback segment only until the instance is brought down again (the shutdown command is used in SQL Worksheet or the Instance Manager is used ”both of which are part of the Enterprise Manager ”to close the Oracle SGA memory area and the Oracle background processes).

The other way to activate a rollback segment is to modify the rollback_segments INIT.ORA parameter. The instance must be shut down and restarted before the init.ora parameter goes into effect. In practice, both activation methods are used so that the rollback segment comes online immediately and is reactivated whenever the instance is restarted.

TIP

To check which rollback segments are currently activated, query the v$rollstat dynamic performance view. It shows only the activated rollback segments. The dba_rollback_segs data dictionary view shows all the rollback segments, whether or not they are activated.

Expanding and Monitoring Rollback Segments

As with tables, rollback segments are allocated additional extents, as needed, automatically and without user intervention. Additional extents might be needed for a rollback segment when many users are making changes, for example, or when a batch update operation causes a large amount of redo log information to be recorded.

Unlike tables, however, rollback segments also can be set to shrink in size. You can specify the OPTIMAL clause that is part of the storage clause when you create the rollback segment. OPTIMAL sets a high watermark size for the rollback segment. If the rollback segment grows larger than this size, it automatically releases the additional extents it was allocated until the amount of storage used falls below the optimal size .

Page 480

Setting the optimal size is useful, for example, when you expect a monthly batch update to make the rollback segment grow and you want to reclaim the storage. Otherwise, the segment remains unused until the end of the next month. Don't set the optimal size too low. If you do, making the rollback segment grow when it needs to and then shrinking it back to the optimal size involves too much system overhead.

TIP

You can query the v$rollstat dynamic performance view to see how many times a rollback segment has been extended and shrunk.

Maintaining Rollback Segments

All rollback segments should be created with the same extent sizes. This means that the initial and next extent parameters specified for the rollback segments are the same. The PCTINCREASE parameter cannot be specified.

You should create a tablespace just for rollback segments. One reason for this is that, if a tablespace contains enabled rollback segments, it cannot be taken offline until the rollback segment is disabled.

Using Tablespaces

A tablespace is the name given to a group of one or more database files. When objects are created, you can specify in which tablespace they will occupy storage. This capability gives you control over where and how much storage is used. You can specify the amount of storage users are allowed to use in each tablespace in the database.

Creating the First New Tablespace

An Oracle database always has a tablespace called SYSTEM. The first file created belongs to this tablespace. Because this is the first tablespace created, the data dictionary is created in this tablespace.

You can create additional tablespaces. To create tablespaces in addition to the SYSTEM tablespace, you must ensure that at least two rollback segments are enabled. One of the segments can be the SYSTEM rollback segment, which is created automatically. As long as at least two rollback segments are enabled, you can create as many tablespaces as you want.

To create a tablespace, issue the following SQL command. It is similar to how filenames are specified with the CREATE DATABASE command:

 SQLPLUS> CREATE TABLESPACE kashmir DATAFILE `\disk01\myfile1.dbs' SIZE 10M; 

Previous Table of Contents Next