1.7 How do I…Examine and change the rollback segment configuration? ProblemI want to review the number, size, and location of the rollback segments for my new Oracle instance. Historical database performance information is not available yet, but I want to alter my database to include a serviceable rollback segment configuration.
Technique
Rollback segments summarize the state of the database prior to any currently uncommitted transactions. They provide read-consistent views of the database and the capability to undo or rollback uncommitted database inserts, updates, or deletes. To view the current rollback segment configuration, query the DBA_ROLLBACK_SEGS data dictionary view and the V$ROLLSTAT and V$ROLLNAME dynamic performance tables. Figures 1.13 and 1.14 describe these views. Create and size rollback segments using the create rollback segment statement.
Steps
1. Run SQL*Plus and connect as the WAITE user account. Use the START command to load and execute the CHP1_3.SQL script.
The results of the script in Figure 1.15 show that one rollback segment exists and it resides in the system tablespace.
SQL> start chp1_3
SQL>
SQL> SELECT segment_name, tablespace_name, initial_extent,
2 next_extent, min_extents, optsize, rs.status
3 FROM dba_rollback_segs rs, v$rollstat vs, v$rollname vn
4 WHERE rs.segment_name = vn.name and
5 vn.usn = vs.usn;
2. Use the START command to load and execute the CHP1_4.SQL script, as shown in Figure 1.16.
NOTE - The script CHP1_4.SQL creates four rollback segments in four separate operations. Figure 1.16 shows only one of these operations to save space.3. Use the START command to load and execute the CHP1_3.SQL script again to see the results of Step 2. The script and its results appear in Figure 1.17.
How It Works
Step 1 queries one data dictionary view and two dynamic performance tables that maintain information about the database’s rollback segment configuration. The script in Step 2 uses the create rollback segment command to create a new rollback segment and makes the new rollback segment available to the Oracle8 server by using the alter rollback segment command to bring the rollback segment online. The final step is to repeat Step 1 to verify the successful creation of the new rollback segment. Unless the parallel option is enabled for an instance, public and private rollback segments are identical.
The rollback segment in the system tablespace is intended for special system transactions. If there are no other rollback segments or no available rollback segments, the Oracle8 server will assign user transactions to the SYSTEM rollback segment. Because Oracle8 often dynamically allocates and deallocates rollback segment storage, this situation can lead to fragmentation in the system tablespace and degraded performance.