Data Blocks, Extents, and Segments
He was not merely a chip of the old block, but the old block itself.
Edmund Burke: On Pitt's first speech
This chapter describes the nature of and relationships between logical storage structures in the Oracle Server. It includes:
If you are using Trusted Oracle, see the Trusted Oracle7 Server Administrator's Guide for more information about storage in that environment.
The Relationships Between Data Blocks, Extents, and Segments
Oracle allocates database space for all data in a database. The units of logical database allocation are data blocks, extents, and segments. The following illustration shows the relationships between these data structures:
Figure 3 - 1. The Relationship Among Segments, Extents, and Data Blocks
At the finest level of granularity, Oracle stores data in data blocks (also called logical blocks, Oracle blocks, or pages). One data block corresponds to a specific number of bytes of physical database space on disk. You set the data block size for every Oracle database when you create the database. This data block size should be a multiple of the operating system's block size within the maximum limit. Oracle data blocks are the smallest units of storage that Oracle can use or allocate.
In contrast, all data at the physical, operating system level is stored in bytes. Each operating system has what is called a block size. Oracle requests data in multiples of Oracle blocks, not operating system blocks. Therefore, you should set the Oracle block size to a multiple of the operating system block size to avoid unnecessary I/O.
The next level of logical database space is called an extent. An extent is a specific number of contiguous data blocks that is allocated for storing a specific type of information.
The level of logical database storage above an extent is called a segment. A segment is a set of extents that have been allocated for a specific type of data structure, and that all are stored in the same tablespace. For example, each table's data is stored in its own data segment, while each index's data is stored in its own index segment.
Oracle allocates space for segments in extents. Therefore, when the existing extents of a segment are full, Oracle allocates another extent for that segment. Because extents are allocated as needed, the extents of a segment may or may not be contiguous on disk. The segments also can span files, but the individual extents cannot.
Oracle manages the storage space in the datafiles of a database in units called data blocks. A data block is the smallest unit of I/O used by a database.
Data Block Format
The Oracle block format is similar regardless of whether the data block contains table, index, or clustered data. Figure 3 - 2 illustrates the format of a data block.
Figure 3 - 2. Data Block Format
Header (Common and Variable)
The header contains general block information, such as the block address and the type of segment; for example, data, index, or rollback. While some block overhead is fixed in size, the total block overhead size is variable. On average, the fixed and variable portions of data block overhead total 84 to 107 bytes.
This portion of the block contains information about the tables having rows in this block.
This portion of the block contains row information about the actual rows in the block (including addresses for each row piece in the row data area).
Once the space has been allocated in the row directory of a block's header, this space is not reclaimed when the row is deleted. Therefore, a block that is currently empty but had up to 50 rows at one time continues to have 100 bytes allocated in the header for the row directory. Oracle only reuses this space as new rows are inserted in the block.
This portion of the block contains table or index data. Rows can span blocks; see "Row Chaining across Data Blocks" .
Free space is used for inserting new rows and for updates to rows that require additional space (for example, when a trailing null is updated to a non-null value). Whether issued insertions actually occur in a given data block is a function of the value for the space management parameter PCTFREE and the amount of current free space in that data block. See "An Introduction to PCTFREE, PCTUSED, and Row Chaining" for more information on space management parameters.
Space Used for Transaction Entries
Data blocks allocated for the data segment of a table, cluster, or the index segment of an index can also use free space for transaction entries. A transaction entry is required in a block for each INSERT, UPDATE, DELETE, and SELECT...FOR UPDATE statement accessing one or more rows in the block. The space required for transaction entries is operating system dependent; however, transaction entries in most operating systems require approximately 23 bytes.
An Introduction to PCTFREE, PCTUSED, and Row Chaining
Two space management parameters, PCTFREE and PCTUSED, allow a developer to control the use of free space for inserts of and updates to the rows in data blocks. You specify these parameters only when creating or altering tables or clusters (data segments). You can also specify the storage parameter PCTFREE when creating or altering indexes (index segments).
The PCTFREE Parameter
The PCTFREE parameter is used to set the percentage of a block to be reserved (kept free) for possible updates to rows that already are contained in that block. For example, assume that you specify the following parameter within a CREATE TABLE statement:
This states that 20% of each data block used for this table's data segment will be kept free and available for possible updates to the existing rows already within each block. Figure 3 - 3 illustrates PCTFREE.
Figure 3 - 3. PCTFREE
Notice that before the block reaches PCTFREE, the free space of the data block is filled by both the insertion of new rows and by the growth of the data block header.
The PCTUSED Parameter
After a data block becomes full, as determined by PCTFREE, Oracle does not consider the block for the insertion of new rows until the percentage of the block being used falls below the parameter PCTUSED. Before this value is achieved, Oracle uses the free space of the data block only for updates to rows already contained in the data block. For example, assume that you specify the following parameter within a CREATE TABLE statement:
In this case, a data block used for this table's data segment is not considered for the insertion of any new rows until the amount of used space in the block falls to 39% or less (assuming that the block's used space has previously reached PCTFREE). Figure 3 - 4 illustrates this.
Figure 3 - 4. PCTUSED
How PCTFREE and PCTUSED Work Together
PCTFREE and PCTUSED work together to optimize the utilization of space in the data blocks of the extents within a data segment. Figure 3 - 5 illustrates how PCTFREE and PCTUSED work together to govern the free space of a data block.
Figure 3 - 5. Maintaining the Free Space of Data Blocks with PCTFREE and PCTUSED
How Oracle Uses PCTFREE and PCTUSED
In a newly allocated data block, the space available for inserts is the block size minus the sum of the block overhead and PCTFREE. Updates to existing data can use any available space in the block; therefore, updates can reduce the available space of a block to less than PCTFREE, the space reserved for updates but not accessible to inserts.
For each data and index segment, Oracle maintains one or more free lists; a free list is a list of data blocks that have been allocated for that segment's extents and have free space greater than PCTFREE; these blocks are available for inserts. When you issue an INSERT statement, Oracle checks a free list of the table for the first available block and uses it if possible; if the free space in that block is not large enough to accommodate the INSERT statement, and it is at least PCTUSED, Oracle takes the block off the free list. Multiple free lists per segment can reduce contention for free lists when concurrent inserts take place.
After you issue DELETE and UPDATE statements, Oracle checks to see if the space being used in the block is less than PCTUSED; if it is, the block goes to the beginning of the free list, and it is the first of the available blocks to be used.
Availability and Compression of Free Space in a Data Block
Two types of statements return space to the free space of one or more data blocks: DELETE statements, and UPDATE statements that update existing values to smaller values. The released space from these types of statements is available for subsequent INSERT statements under the following conditions:
- If the INSERT statement is in the same transaction and subsequent to the statement that frees space, the INSERT statement can use the space made available.
Released space may or may not be contiguous with the main area of free space in a data block. Oracle coalesces the free space of a data block only when an INSERT or UPDATE statement attempts to use a block that contains enough free space to contain a new row piece, yet the free space is fragmented so that the row piece cannot be inserted in a contiguous section of the block. Oracle does this compression only in such situations so that the performance of a database system is not decreased by the continuous and unnecessary compression of the free space in data blocks as each DELETE or UPDATE statement is issued.
- If the INSERT statement is in one transaction and the statement that frees space is in a second transaction (perhaps being executed by another user), the INSERT statement can only use the space made available after the second transaction commits, and only if the space is needed.
Row Chaining across Data Blocks
In some circumstances, all of the data for a row in a table may not be able to fit in the same data block. When this occurs, Oracle stores the data for the row in a chain of data blocks (one or more) reserved for that segment. Row chaining most often occurs with large rows (for example, rows that contain a column of datatype LONG or LONG RAW).
Note: The format of a row and a row piece are described in "Row Format and Size" .
If a table contains a column of datatype LONG, which can hold up to two gigabytes of information, the data for a row may need to be chained to one or more data blocks. Nothing can be done to avoid this type of row chaining.
If a row in a data block is updated so that the overall row length increases and the block's free space has been completely filled, the data for the entire row is migrated to a new data block, assuming the entire row can fit in a new block. Oracle preserves the original row piece of a migrated row to point to the new block containing the migrated row; the ROWID of a migrated row does not change.
When a row is chained or migrated, I/O performance associated with this row decreases because Oracle must scan more than one data block to retrieve the information for the row. For information about reducing migrated rows and improving I/O performance, see Oracle7 Server Tuning.
An extent is a logical unit of database storage space allocation made up of a number of contiguous data blocks. Each segment is composed of one or more extents. When the existing space in a segment is completely used, Oracle allocates a new extent for the segment.
This section describes how extents are allocated for segments.
When Extents Are Allocated for Segments
No matter what type, each segment in a database is created with at least one extent to hold its data. This extent is called the segment's initial extent.
Note: Rollback segments always have at least two extents.
For example, when you create a table, its data segment contains an initial extent of a specified number of data blocks. Although no rows have been inserted yet, the Oracle data blocks that correspond to the initial extent are reserved for that table's rows.
If the data blocks of a segment's initial extent become full and more space is required to hold new data, Oracle automatically allocates an incremental extent for that segment. An incremental extent is a subsequent extent of the same or greater size than the previous extent in that segment. The next section explains the factors controlling the size of incremental extents.
For maintenance purposes, each segment in a database contains a segment header block that describes the characteristics of that segment and a directory (list) of the extents in that segment.
Extents and the Parallel Query Option
When you use the parallel query option to create indexes and non-clustered tables in parallel, each query server allocates a new extent and fills the extent with the table or index's data. Thus, if you create an index with a degree of parallelism of three, there will be at least three extents for that index initially.
Serial operations require the object to have at least one extent. Parallel creations require that non-clustered tables or indexes have at least as many extents as there are query servers that create the object.
When you create a table or index with the parallel query option, it is possible to create "pockets" of free space. This occurs when you specify more query servers than there are datafiles in the tablespace. Oracle cannot coalesce this free space with other free space, so this space is available only for subsequent inserts into that table.
For example, if you specify a degree of parallelism of three for a CREATE TABLE ... AS <subquery> statement but there is only one datafile in the tablespace, the situation illustrated in Figure 3 - 6 can arise. Oracle can only coalesce the free space in the last extent of a table or index in each datafile, so all "pockets" of free space within internal table extents of a datafile cannot be coalesced with other free space and allocated as extents.
Figure 3 - 6. Unusable Free Space
To alleviate the free space problem, set the degree of parallelism to less than or equal to the number of datafiles in the tablespace that you are placing the non-clustered table or index. Oracle assigns query servers to datafiles in a round-robin fashion, so specifying fewer query servers than datafiles ensures that all free space can be used later by all tables in the tablespace for subsequent extent allocation.
For more information about the parallel query option and creating non-clustered tables and indexes in parallel, see Oracle7 Server Tuning.
For more information about datafiles and tablespaces, see Chapter 4, "Tablespaces and Datafiles".
How Extents Are Allocated for Segments
Oracle controls the allocation of extents for a given segment. The procedure to allocate a new extent for a segment is as follows:
1. Oracle searches through the free space (in the tablespace that contains the segment) for the first free, contiguous set of data blocks of an incremental extent's size or larger. Oracle finds the free space for the new extent by using the following algorithm:
- 1.1 Oracle searches for a contiguous set of data blocks that matches the size of new extent, then adds one block to reduce internal fragmentation. For example, if a new extent requires 19 data blocks, Oracle searches for exactly 20 contiguous data blocks. However, if the new extent is 5 or fewer blocks, Oracle does not add an extra block to the request.
- 1.2 If an exact match is not found, Oracle then searches for a set of contiguous data blocks equal to or greater than the amount needed. If Oracle finds a group of contiguous blocks that is at least five blocks greater than the size of the extent that is needed, it splits the group of blocks into separate extents, one of which is the size it needs; if Oracle finds a group of blocks that is larger than the size it needs, but less than five blocks larger, it allocates all the contiguous blocks.
Continuing with the example, if Oracle does not find a set of exactly 20 contiguous data blocks, Oracle then searches for a set of contiguous data blocks greater than 20. If the first set that Oracle finds contains 25 or more blocks, it breaks the blocks up and allocates twenty of them to the new extent. Otherwise, it allocates all of the blocks (between 21 and 24) to the new extent.
- 1.3 If Oracle does not find a larger set of contiguous data blocks, Oracle then coalesces any free, adjacent data blocks in the corresponding tablespace so that larger sets of contiguous data blocks are formed. (The SMON background process also periodically coalesces adjacent free space.) After coalescing a tablespace's data blocks, Oracle performs the searches described in 1.1. and 1.2.. again. If an extent cannot be allocated after the second search, Oracle returns an error.
2. Once Oracle finds the necessary free space in the tablespace, Oracle allocates a portion of the free space that corresponds to the size of the incremental extent. If Oracle had found a larger amount of free space than was required for the extent, Oracle leaves the remainder as free space (no smaller than five contiguous blocks).
3. Oracle updates the segment header and data dictionary to show that a new extent has been allocated and that the allocated space is no longer free.
Usually, Oracle clears the blocks of a newly allocated extent when the extent is first used. In a few cases, however, such as when a database administrator issues an ALTER TABLE or ALTER CLUSTER statement with the ALLOCATE EXTENT option while using free list groups, Oracle clears the extent's blocks when it allocates the extent.
When Extents Are Deallocated
In general, the extents of a segment do not return to the tablespace until you drop the object whose data is stored in the segment (using a DROP TABLE or DROP CLUSTER statement). Exceptions to this include the following:
- The owner of a table or cluster, or a user with the DELETE ANY privilege, can truncate the table or cluster with a TRUNCATE...DROP STORAGE statement.
- Periodically, Oracle may deallocate one or more extents of a rollback segment.
- A DBA can deallocate unused extents using the following SQL syntax:
ALTER TABLE table_name DEALLOCATE UNUSED
For More Information on Deallocating Extents
See Oracle7 Server Administrator's Guide and Oracle7 Server SQL Reference.
When extents are freed, Oracle updates the data dictionary to reflect the regained extents as available space. All data in the blocks of freed extents is inaccessible, and Oracle clears out the data when the blocks are subsequently reused for other extents.
Non-Clustered Tables, Snapshots, and Snapshot Logs
As long as a non-clustered table (including an underlying table for a snapshot or snapshot log) exists or until you truncate the table, any data block allocated to its data segment remains allocated for the table; Oracle inserts new rows into a block if there is enough room. Even if you delete all rows of a table, Oracle does not reclaim the data blocks for use by other objects in the tablespace.
When you drop a non-clustered table, Oracle reclaims all the extents of its data and index segments for the tablespaces that they were in and makes the extents available for other objects in the tablespace. Subsequently, when other segments require large extents, Oracle identifies and combines contiguous reclaimed extents to form the requested larger extents.
Clustered Tables and Snapshots
Clustered tables and snapshots store their information in the data segment created for the cluster. Therefore, if you drop a clustered table, the data segment remains for the other tables in the cluster, and no extents are deallocated. You can also truncate clusters (except for hash clusters) to free extents.
All extents allocated to an index segment remain allocated as long as the index exists. When you drop the index or associated table or cluster, Oracle reclaims the extents for other uses within the tablespace.
Oracle periodically checks to see if the rollback segments of the database have grown larger than their optimal size. If a rollback segment is larger than is optimal (that is, it has too many extents), then Oracle automatically deallocates one or more extents from the rollback segment. See "How Extents Are Deallocated from a Rollback Segment" for more information.
When Oracle completes the execution of a statement requiring a temporary segment, Oracle automatically drops the temporary segment and returns the extents allocated for that segment to the associated tablespace.
Determining Sizes and Limits of Segment Extents
Storage parameters expressed in terms of extents define every segment. Storage parameters apply to all types of segments. They control how Oracle allocates free database space for a given segment. For example, you can determine how much space is initially reserved for a table's data segment or you can limit the number of extents the table can allocate by specifying the storage parameters of a table in the STORAGE clause of the CREATE TABLE statement.
A segment is a set of extents that contain all the data for a specific logical storage structure within a tablespace. For example, for each table, Oracle allocates one or more extents to form that table's data segment, and, for each index, Oracle allocates one or more extents to form its index segment.
There are four types of segments used in Oracle databases:
The following sections discuss each type of segment.
Every non-clustered table (including snapshots and snapshot logs) in an Oracle database has a single data segment to hold all of its data. Oracle creates this data segment when you create the object with the CREATE TABLE/SNAPSHOT/SNAPSHOT LOG command.
Every cluster in an Oracle database uses a single data segment to hold the data for all of its tables. Oracle creates the data segment for the cluster when you issue the CREATE CLUSTER command.
The storage parameters for a table, snapshot, snapshot log, or cluster control the way that its data segment's extents are allocated. You can set these storage parameters directly with the CREATE TABLE/ SNAPSHOT/ SNAPSHOT LOG/CLUSTER or ALTER TABLE/ SNAPSHOT/ SNAPSHOT LOG/CLUSTER commands; these affect the efficiency of data retrieval and storage for the data segment associated with the object. For more information on the various CREATE and ALTER commands, see the Oracle7 Server SQL Reference.
Every index in an Oracle database has a single index segment to hold all of its data. Oracle creates the index segment for the index when you issue the CREATE INDEX command. This command allows you to specify the storage parameters for the extents of the index segment and the tablespace in which to create the index segment. (The segments of a table and an index associated with it do not have to occupy the same tablespace.) Setting the storage parameters directly affects the efficiency of data retrieval and storage.
Each database contains one or more rollback segments. A rollback segment is a portion of the database that records the actions of transactions if the transaction should be rolled back (undone). Rollback segments are used to provide read consistency, to rollback transactions, and to recover the database.
For specific information about how rollback segments function in these situations, see the appropriate sections of this book:
Contents of a Rollback Segment
Information in a rollback segment consists of several rollback entries. Among other information, a rollback entry includes block information (the filename and block ID corresponding to the data that was changed) and the data as it existed before an operation in a transaction. Oracle links rollback entries for the same transaction, so the entries can easily be found if necessary for transaction rollback.
Database users or administrators cannot access or read rollback segments; only Oracle can write to or read them. (They are owned by the user SYS, no matter which user creates them.)
Because rollback entries change data blocks, Oracle also records changes to them in the redo log. This second recording of the rollback information is very important for active transactions not yet committed at the time of the system crash. If a system crash occurs, Oracle automatically restores the rollback segment information, including the rollback entries for active transactions, as part of instance or media recovery. Oracle performs rollbacks of transactions that had not been committed or rolled back at the time of the failure after recovery is complete.
When Rollback Information Is Required
Oracle maintains a transaction table for each rollback segment contained in a database. Each table is a list of all transactions that use the associated rollback segment and the rollback entries for each change performed by these transactions. Oracle uses the rollback entries in a rollback segment to perform a transaction rollback and to create read-consistent results for queries.
Rollback segments record the data prior to change on a per transaction basis. For every transaction, Oracle links each new change to the previous change. If you must roll back the transaction, Oracle applies the changes in the chain to the data blocks in an order that restores the data to its previous state.
Similarly, when Oracle needs to provide a read-consistent set of results for a query, it can use information in rollback segments to create a set of data consistent with respect to a single point in time.
All types of rollbacks use the same procedures:
- statement level rollback (due to statement or deadlock execution error)
- rollback of a transaction due to user request
- rollback of a transaction due to abnormal process termination
- rollback of all outstanding transactions when an instance terminates abnormally
- rollback of incomplete transactions during recovery
Transactions and Rollback Segments
Each time a user's transaction begins, Oracle assigns the transaction to a rollback segment:
For the duration of a transaction, the associated user process writes rollback information only to the assigned rollback segment.
- Oracle can assign a transaction automatically to the next available rollback segment. The transaction assignment occurs when you issue the first DML or DDL statement in the transaction. Oracle never assigns read-only transactions (transactions that contain only queries) to a rollback segment, regardless of whether the transaction begins with a SET TRANSACTION READ ONLY statement.
When you commit a transaction, Oracle releases the rollback information, but does not immediately destroy it. The information remains in the rollback segment to create read-consistent views of pertinent data for queries that started before the transaction committed. To guarantee that rollback data is available for as long as possible for such views, Oracle writes the extents of rollback segments sequentially. When the last extent of the rollback segment becomes full, Oracle continues writing rollback data by wrapping around to the first extent in the segment. A long-running transaction (idle or active) may require a new extent to be allocated for the rollback segment. See Figure 3 - 7, Figure 3 - 8, and Figure 3 - 9 for more information about how transactions use the extents of a rollback segment.
Each rollback segment can handle a certain number of transactions from one instance. Unless you explicitly assign transactions to particular rollback segments, Oracle distributes active transactions across available rollback segments so that all rollback segments are assigned approximately the same number of active transactions. Distribution does not depend on the size of the available rollback segments. Therefore, in environments where all transactions generate the same amount of rollback information, all rollback segments can be the same size.
Additional Information: The number of transactions that a rollback segment can handle is an operating system-specific function of the data block size. See your Oracle operating system-specific documentation for more information.
How Extents Are Used and Allocated for Rollback Segments
When you create a rollback segment, you can specify storage parameters to control the allocation of extents for that segment. Each rollback segment must have at least two extents allocated.
A transaction writes sequentially to a single rollback segment. Each transaction writes to only one extent of the rollback segment at any given time. Furthermore, many active transactions (transactions in progress, not committed or rolled back) can write concurrently to a single rollback segment, even the same extent of a rollback segment; however, each block in a rollback segment's extent can contain information for a single transaction only.
When a transaction runs out of space in the current extent and needs to continue writing, Oracle must find an available extent of the same rollback segment in which to write. Oracle has two options:
- It can reuse an extent already allocated to the rollback segment.
The first transaction that needs to acquire more rollback space checks the next extent of the rollback segment. If the next extent of the rollback segment does not contain active undo information, Oracle makes it the current extent, and all transactions that need more space from then on can write rollback information to the new current extent. Figure 3 - 7 illustrates two transactions, T1 and T2, which continue writing from the third extent to the fourth extent of a rollback segment.
- It can acquire (and allocate) a new extent for the rollback segment.
Figure 3 - 7. Use of Allocated Extents in a Rollback Segment
As the transactions continue writing and fill the current extent, Oracle checks the next extent already allocated for the rollback segment to determine if it is available. In Figure 3 - 8, when E4 is completely full, T1 and T2 continue any further writing to the next extent allocated for the rollback segment that is available; in this figure, E1 is this extent. This figure shows the cyclical nature of extent use in rollback segments.
Figure 3 - 8. Cyclical Use of the Allocated Extents in a Rollback Segment
To continue writing rollback information for a transaction, Oracle always tries to reuse the next extent in the ring first. However, if the next extent contains active data, then Oracle must allocate a new extent. Oracle can allocate new extents for a rollback segment until the number of extents reaches the value set for the rollback segment's storage parameter MAXEXTENTS.
Figure 3 - 9 shows when a new extent must be allocated for a rollback segment. The uncommitted transactions are long running (either idle, active, or persistent in-doubt distributed transactions). At this time, they are writing to the fourth extent, E4, in the rollback segment. However, when E4 is completely full, the transactions cannot continue further writing to the next extent in sequence, E1, because it contains active rollback entries. Therefore, Oracle allocates a new extent, E5, for this rollback segment, and the transactions continue writing to this new extent.
Figure 3 - 9. Allocation of a New Extent for a Rollback Segment
How Extents Are Deallocated from a Rollback Segment
When you create or alter a rollback segment, you can use the storage parameter OPTIMAL, which applies only to rollback segments, to specify the optimal size of the rollback segment in bytes. If a transaction needs to continue writing rollback information from one extent to another extent in the rollback segment, Oracle compares the current size of the rollback segment to the segment's optimal size. If the rollback segment is larger than its optimal size and the extents immediately following the extent just filled are inactive, Oracle deallocates consecutive non-active extents from the rollback segment until the total size of the rollback segment is equal to or as close to but not less than its optimal size. Oracle always frees the oldest inactive extents, as these are the least likely to be used by consistent reads. A rollback segment's OPTIMAL setting cannot be less than the combined space allocated for the minimum number of extents for the segment:
(INITIAL + NEXT + NEXT + ... up to MINEXTENTS) bytes
When you drop a rollback segment, Oracle returns all extents of the rollback segment to its tablespace. The returned extents are then available to other segments in the tablespace.
Instances and Types of Rollback Segments
When an instance opens a database, it must acquire one or more rollback segments so that the instance can handle rollback information produced by subsequent transactions. An instance can acquire both private and public rollback segments. A private rollback segment is acquired explicitly by an instance when the instance opens a database. Public rollback segments form a pool of rollback segments that any instance requiring a rollback segment can use.
Any number of private and public rollback segments can exist in a database. As an instance opens a database, the instance attempts to acquire one or more rollback segments according to the following rules:
CEIL is a SQL function that returns the smallest integer greater than or equal to the numeric input. In the example above, if TRANSACTIONS equal 155 and TRANSACTIONS_PER_ROLLBACK_SEGMENT equal 10, then the instance will try to acquire at least 16 rollback segments.
Note: The TRANSACTIONS_PER_ROLLBACK_SEGMENT parameter does not limit the number of transactions that can use a rollback segment. Rather, it determines the number of rollback segments an instance attempts to acquire when opening a database.
- If the instance already has acquired enough private rollback segments in Step 2, no further action is required. However, if an instance requires more rollback segments, the instance attempts to acquire public rollback segments. (An instance can open the database even if the instance cannot acquire the number of rollback segments given by the division above.)
Once an instance claims a public rollback segment, no other instance can use that segment until either the rollback segment is taken offline or the instance that claimed the rollback segment is shut down.
Note: A database used by the Oracle Parallel Server optionally can have only public and no private segments, as long as the number of segments in the database is high enough to ensure that each instance that opens the database can acquire at least two rollback segments, one of which is the SYSTEM rollback segment (see the following section). However, when using the Oracle Parallel Server, you may want to use private rollback segments. See Oracle7 Parallel Server Concepts & Administration for more information about rollback segment use in an Oracle Parallel Server.
The Rollback Segment SYSTEM
Oracle creates an initial rollback segment called SYSTEM whenever a database is created. This segment is in the SYSTEM tablespace and uses that tablespace's default storage parameters. You cannot drop the SYSTEM rollback segment. An instance always acquires the SYSTEM rollback segment in addition to any other rollback segments it needs.
If there are multiple rollback segments, Oracle tries to use the SYSTEM rollback segment only for special system transactions and distributes user transactions among other rollback segments; if there are too many transactions for the non-SYSTEM rollback segments, Oracle uses the SYSTEM segment as necessary. In general, after database creation, you should create at least one additional rollback segment in the SYSTEM tablespace.
Rollback Segment States
A rollback segment is always in one of several states, depending on whether it is offline, acquired by an instance, involved in an unresolved transaction, in need of recovery, or dropped. The state of the rollback segment determines whether it can be used in transactions, as well as which administrative procedures a DBA can perform on it.
The rollback segment states are the following:
Has not been acquired (brought online) by any instance.
Has been acquired (brought online) by an instance; may contain data from active transactions.
Contains data from uncommitted transactions that cannot be rolled back (because the data files involved are inaccessible), or is corrupted.
Contains data from an in-doubt transaction (that is, an unresolved distributed transaction).
Has been dropped (The space once allocated to this rollback segment will later be used when a new rollback segment is created).
Figure 3 - 10 shows how a rollback segment moves from one state to another.
Figure 3 - 10. Rollback Segment States and State Transitions
PARTLY AVAILABLE and NEEDS RECOVERY Rollback Segments The PARTLY AVAILABLE and NEEDS RECOVERY states are very similar: a rollback segment in either state usually contains data from an unresolved transaction. The differences between the two states are the following:
If you bring a PARTLY AVAILABLE rollback segment online (by a command or during instance startup), Oracle can use it for new transactions. However, the in-doubt transaction still holds some of its transaction table entries, so the number of new transactions that can use the rollback segment is limited. (See "When Rollback Information Is Required" for information on the transaction table.)
Also, until you resolve the in-doubt transaction, the transaction continues to hold the extents it acquired in the rollback segment, preventing other transactions from using them. Thus, the rollback segment might need to acquire new extents for the active transactions, and therefore grow. To prevent the rollback segment from growing, a database administrator might prefer to create a new rollback segment for transactions to use until the in-doubt transaction is resolved, rather than bring the PARTLY AVAILABLE segment online.
Viewing the State of a Rollback Segment The data dictionary table DBA_ROLLBACK_SEGS lists the status (state) of each rollback segment, along with other rollback segment information.
Deferred Rollback Segments
When a tablespace goes offline such that transactions cannot be rolled back immediately, Oracle writes a deferred rollback segment. The deferred rollback segment contains the rollback entries that could not be applied to the tablespace, so they can be applied when the tablespace comes back online. These segments disappear as soon as the tablespace is brought back online and recovered. Oracle automatically creates deferred rollback segments in the SYSTEM tablespace.
When processing queries, Oracle often requires temporary workspace for intermediate stages of SQL statement processing. Oracle automatically allocates this disk space called a temporary segment. Typically, Oracle requires a temporary segment as a work area for sorting. Oracle does not create a segment if the sorting operation can be done in memory or if Oracle finds some other way to perform the operation using indexes.
Operations Requiring Temporary Segments
The following commands may require the use of a temporary segment:
For example, if a query contains a DISTINCT clause, a GROUP BY, and an ORDER BY, Oracle can require as many as two temporary segments. If applications often issue statements in the list above, the database administrator may want to improve performance by adjusting the initialization parameter SORT_AREA_SIZE. For more information on SORT_AREA_SIZE and other initialization parameters, see the Oracle7 Server Reference.
- certain correlated subqueries
How Temporary Segments Are Allocated
Oracle allocates temporary segments as needed during a user session. For example, a user might issue a query that requires three temporary segments. Oracle drops temporary segments when the statement completes. The default storage characteristics of the containing tablespace determine those of the extents of the temporary segment.
Oracle creates temporary segments in the temporary tablespace of the user issuing the statement. You specify this tablespace with a CREATE USER or an ALTER USER command using the TEMPORARY TABLESPACE option. Otherwise, the default temporary tablespace is the SYSTEM tablespace. For more information about assigning a user's temporary segment tablespace, see Chapter 17, "Database Access".
Because allocation and deallocation of temporary segments occur frequently, it is reasonable to create a special tablespace for temporary segments. By doing so, you can distribute I/O across disk devices, and you may avoid fragmentation of the SYSTEM and other tablespaces that otherwise would hold temporary segments.
The redo log does not contain entries for changes to temporary segments used for sort operations.