Oracle allocates logical database space for all data in a database. The units of database space allocation are data blocks, extents, and segments.
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.
The next level of logical database space is an extent. An extent is a specific number of contiguous data blocks allocated for storing a specific type of information.
A segment is a set of extents, each of which has been allocated for a specific data structure and all of which are stored in the same table-space. Each table’s data is stored in its own data segment, which each index’s data is stored in its own index segment. If the table or index is partitioned, each partition is stored in its own segment. 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. A segment and all its extents are stored in one table-space. Within a tablespace, a segment can include extends from more than one file; The segment can span datafiles. Each extent can contain data from only one datafile.
If you allocate an extent to a specific instance(Manually storage management), the blocks are immediately allocated to the free list. However, if the extent is not allowed to a specific instance(Automatically storage management), then the blocks themselves are allocated only when the high water mark moves. The high water mark is the boundary between used and unused space in a segment.
Data blocks
Storage space in the datafiles of a database is managed in units called data blocks. At the OS level, all data is stored in bytes. Oracle block size is different from system blocks.
The standard block size is specified by the DB_BLOCK_SIZE initialization parameter.
Data block format is similar regardless of whether the data block contains table, index, or clustered data.
A. common and variable Header: the header contains general block information, such as the block address and the type of segment ( for example, data or index).
B. Table directory: This portion of the data block contains information about the table having rows in this block.
C. Row directory: contains information about the actual rows in the block(including addresses for each row piece in the row data area). The space for a row is not reclaimed when the row is deleted. Oracle reuses this space only when new rows are inserted in the block.
E. Overhead: The data block header, table directory, and row directory are referred to collectively as overhead.
F. Row Data: contains table or index data, rows can span blocks.
G. Free space: for insertion of new rows and for updates to rows that requir additional space. In data blocks allocated for the data segment of a table or cluster, or for the index segment of an index, free space can also hold 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 transaction entry tells that the block is now updating by this transaction, so others have to wait?????
Free Space Management
Free space can be managed automatically or manually.
Free space can be managed automatically inside database segments. The in-segment free/used space is tracked using bitmaps, as opposed to free lists. You specify automatic segment-space management when you create a locally managed tablespace. The specification then applies to all segments subsequently created in this tablespace.
Availability and Optimization of Free Space in a Data Block
Two types of statement can increase the free space of one or more data blocks: DELETE statement, 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.
1. If the INSERT statement is in the same transaction and subsequent to the statement that frees space, then the INSERT statement can use the space made available
2. If the INSERT statement is in a separate transaction from the statement that frees space, then the INSERT statement can use the space made available only after the other transaction commits and only if the space is needed.
Based in pre-conditions, Oracle will compress free spaces in a block.
Row Chaining and Migrating
In two circumstances, the data for a row in a table maybe too large to fit into a single data block.
1. The rows is too large to fit into one data block when it is first inserted. Then Oracle stores the data for the row in a chain of data blocks reserved for that segment. Row chaining most often occurs with large rows, such as rows that contain a column of data types LONG or LONG RAW. Row chaining in these cases is unavoidable.
2. A row that originally fit into one data block is updatable so that the overall row length increases, and the block’s free space is already completely filled. Oracle migrates the data for the entire row 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.
PCTFREE, PCTUSED and Row Chaining
For manually managed tablespaces, two space management parameters: PCTFREE and PCTUSED enable you to control the use of the free space of inserts and updatesd to the rows in all the data blocks of a particular segment.
PCTFREE: sets the minimum percentage of a data block to be reserved as free space for possible updates to rows that already exist in that block. If set PCTFREE=20, this states 20% of each data block in this table’s data segment be kept free and available for possible updates to the existing rows already within each block. New rows can be added to the row data area, and corresponding information can be added to the variable portions of the overhead area until the row data and overhead total 80% of the total block size.
PCTUSED: set the minimum percentage of a block that can be used for row data plus overhead before new rows are added to the block. After a data block is filled to the limit determined by PCTFREE, oracle considers the block unavailable for the insertion of new rows until the percentage of that block falls beneath the parameter PCTUSED. Until this value is achieved, Oracle uses the free space of the data block only for updates to rows already contained in the data block.
Which means with PCTFREE and PCTUSED parameters, the free space is sometimes only available to update commands.
For each data and index segment, oracle maintains one or more free lists---lists of data blocks that have been allocated for that segment’s extents and have free space greater than PCTFREE. This blocks are available for inserts. If the free space in that block is not large enough and the block is at least PCTUSED, then Oracle takes the block off the free list. Multiple free lists for each segment can reduce contention for free lists when concurrent inserts take place.
After issue a DELETE or UPDATE statement, Oracle processes the statement and checkes to see if the space being used in the block is now less than PCTUSED. If it is, then the block goes to the beginning of the transaction free list, and it is the first of the available blocks to be used in that transaction.
Overview of Extents
An Extent is made up a number of contiguous data blocks. When the existing space in a segment is completely used, Oracle allocates a new extent for the segment.
When create a table, Oracle allocates to the table’s data segment an initial extent of a specified number of data blocks. When the initial extent become full and more space is required, Oracle automatically allocates an incremental extent for that segment. An incremental extent is a subsequent extent of the same or greater size than the previously allocated extent in that segment.
The header block of each segment contains a directory of the extents in that segment.
Storage parameters expressed in terms of extents define every segment.
Data dictionary managed table-space and locally managed table-space by bitmap.
The extents of a segment do not return to the table-space until you drop the schema object whose data is stored in the segment with some exceptions:
When extents are freed, Oracle modifies the bitmap in the data-file(for locally managed table-space) or updates the data dictionary ( for dictionary managed table-space) to reflect the regained extents as available space. Any data in the blocks of freed extents becomes inaccessible.
Extents in Non-clustered tables
As long as a non-clustered table exists or until you truncate the table, any data block allocated to its data segment remains allocated for the table. Even if you delete all rows of a table, oracle does not reclaim the data blocks for use by other objects in the table-space. After the dropping of a non-clustered table, this space can be reclaimed when other extents require free space. Oracle reclaims all the extents and makes them available for other schema objects in the same tablespace.
In dictionary managed table-space,when a segment requires an extent larger than the available extents, Oracle identifies and combines contiguous reclaimed extents to form a larger one. this is called coalescing extents.
For clustered tables, if you drop one table in a cluster, the data segment remains for the other tables in the cluster, and no extents are de-allocated. 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.
Extents in Temporary Segments
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 table-space. Multiple sorts, can use sort segments in temporary tablespaces designated exclusively for sorts. The sort segments are allocated only once for the instance and they are not returned after the sort, but remain available for other multiple sorts. A temporary segment in a temporary table contains data for multiple statements of a single transaction or session. Oracle drops the temporary segment at the end of the transaction or session, returning the extents allocated for that segment to the associated tables. So, there are two categories of temporary table-space, the one for temporary table and the one for multiple sorts.
Extents in Rollback Segments
Oracle periodically checks the rollback segments of the database to see if they 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 de-allocates one or more extents from the roll-back segment.
Overview of Segments
A segment is a set of extents that contains all the data for a specific logical storage structure within a table-space.
Data Segments
A single data segment in an Oracle database holds all of the data for one of the following:
1. A table that is not partitioned or clustered
2. A partition of a partitioned table
3. A cluster of tables
The specified storage parameters when creating the table/cluster affect the efficiency of data retrieval and storage for the data segment associated with the object.
Index Segment
Every non-partitioned index in an Oracle database has a single index segment to hold all of its data. For a partitioned index, every partition has a single index segment to hold its data. Also the storage parameters directly affects the efficiency of data retrieval and storage.
Temporary Segments
A. When processing queries, Oracle often requires temporary workspace for intermediate stages of SQL statement parsing and execution. Oracle automatically allocates this disk space called a temporary segment. Typically, Oracle requires a temporary segments as a database 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 that requires temporary segments include: CREATE INDEX/SELECT …ORDER/ SELECT DISTINCT/SELECT ..GROUP/SELECT …UNION/ SELECT …INTERSECT/SELECT …MINUS
B. Segments in Temporary tables and their indexes. Oracle can also allocate temporary segments for temporary tables and indexes created on temporary tables. Temporary tables hold data that exists only for the duration of a transaction or session.
Oracle allocates temporary segments differently for queries and temporary tables.
A. Allocation of Temporary segments for queries: If no temporary table-space is defined for the user, then the default temporary table-space is the SYSTEM table-space. Oracle drops temporary segments when the statement completes.
B. Oracle allocates segments for a temporary table when the first INSERT into that table is issued. The first INSERT into a temporary table allocates the segments for the table and its indexes, creates the root page for the indexes, and allocates any LOB segments. Segments for a temporary table are allocated in a temporary table-space for the user who created the temporary table. Oracle drops segments for a transaction/session-specific temporary table at the end of the transaction/session-specific temporary table at the end of the transaction/session. If other transactions/sessions share the use of that temporary table, the segments containing their data remain in the table. Means a temporary segment for every transaction/session.
Automatic Undo Management
Oracle maintains information to nullify changes made to the database. This information consists of records of the actions of transactions, collectively known as undo. Oracle use undo to do the following:
A. Rollback an active transaction
B. Recover a terminated transaction
C. Provide read consistency
D. Recovery from logical corruptions
Oracle recommends you use undo tablespaces to manage undo rather rollback segments.
Undo mode provides a more flexible way to migrate from manual undo ma