3.3 Oracle data storage

---

The tablespace is composed of one or more data segments, composed of extents. Each extent comprises one or more Oracle blocks and an Oracle block comprises one or more disk blocks.

3.3.1 Tablespace

Tablespaces are the logical unit of storage in Oracle. A tablespace is used to group related logical structures together. In Oracle, a database is created with a minimum of one tablespace for the system and one or more user-defined tablespaces. Tablespaces help in the distribution of data and in manageability. Within a tablespace a specific number of data block allocations is called an extent.

There are various kinds of tablespace definitions, each having specific features with specific functionality. The different kinds of tablespaces are discussed in the following sections.

Dictionary-managed tablespaces

Before Oracle 8i, there were no specific classifications for tablespace management. All tablespaces were managed through the data dictionary. Under this system, Oracle stored all tablespace management informa tion in the data dictionary and thus every change to the tablespace caused a write to the data dictionary. This writing of tablespace management information to the data dictionary causes considerable overhead.

When Oracle requires the allocation of extents to the tablespace (to write data), this operation causes a change in the extent value. This change causes the management of extents and values of current extent and the block address to be managed by writing the information to the data dictionary. When information is written to the data dictionary, it creates rollback entries. The information written to the rollback segments is not for use when the user applies a rollback command, but is for use during the recovery process. When the database is abnormally shut down and the database goes into recovery mode, these rollback entries have to be applied in order to get the database to the current state. Dictionary-managed tablespaces is the default method, meaning no specific syntax is required while defining this type of tablespace.

Locally managed tablespaces

With the release of Oracle 8i, the activities of managing extents could be maintained locally within the tablespace. Under this option the tablespace manages its own extents and maintains a bitmap in each data file to keep track of the free or used status of blocks in that data file. In a locally managed tablespace (LMT), each bit in a bitmap corresponds to a block or a group of blocks. Unlike a dictionary-managed tablespace, an update to the dictionary is not made when the allocation of an extent is freed for reuse. However, Oracle server changes the bitmap values to show the new status of the blocks.

Figure 3.7 illustrates the structure of a locally managed tablespace. Apart from the file header information, Oracle also maintains additional information in the form of bitmaps related to the tablespace.

Figure 3.7: Locally managed tablespace.

Oracle writes all required information, such as block address, free or used state of blocks, etc., of the tablespace into its header. All information is stored locally in a bitmap format. All required information is stored and managed locally, and since no updates to the dictionary are required, no rollback entries are performed. This only applies to tablespace manage ment and not to the actual data.

With the reduced update activity to the data dictionary to manage extent activity, locally managed tablespaces have a better performance advantage. Recursive operations are eliminated, because queries against FET$ and UET$ required under dictionary-managed space allocation is not required. While extents are being made, Oracle also checks the adjacent free space and eliminates the need for coalescing of free space.

Locally managed tablespaces are created with a specific LOCAL clause during tablespace creation. Locally managed tablespaces could be converted into dictionary-managed tablespaces, and vice versa.

When adding data files to a locally managed tablespace, additional bytes of space depending on the extent size should be added to the total size of the data file. This additional extent size is used for extent management at the data file level.

When locally managed tablespaces are used, the segment definition parameters such as PCTUSED, FREELIST, and FREELIST GROUPS are ignored. However, parameters such as INITRANS, MAXTRANS,and PCTFREE are valid values.

Oracle 10g 

New Feature: Starting with Oracle 10g, when the MAXTRANS parameter is not required, Oracle assigns the value of 255 by default.

Transportable tablespaces

When data has to be moved from one database to another, the normal methods adopted have been either to export and then import on the target database or to unload and then load. This has been done using tools such as SQL*Loader into the target database or using tools such as Oracle Advanced Replication (OAR).

In Oracle 8i, transportable tablespaces were introduced. As the name implies, transportable tablespaces enabled the transport of the tablespace from one database to another. Transportable tablespace helps in movement of data from one database to another very easily as an alternative to copy, export/import or unload/load operations. It is a matter of copying the tablespace to another location, or another machine, and defining the tablespace in the database. Another great usage of the transportable tablespace is for the archive operation. Tablespaces can be archived into media for future restoration if required.

The issues that tablespaces could be copied only between similar opera ting systems, and that tablespaces could be transported only between Oracle databases, are two limitations that apply to transportable tablespaces.

Oracle 10g 

New Feature: Starting with Oracle 10g, tablespaces could be transported between different operating systems. This is achieved by using the RMAN CONVERT command either before the transport or after the transport at the target location.

Temporary tablespaces

Oracle has created the tablespace of type temporary mainly for sort opera tions. When a sort operation is too large to fit into the local sort area, Oracle uses the temporary tablespace for these operations. Sort operations can occur during various types of SQL query operations, for example when an ORDER BY or GROUP BY operation is involved.

Oracle 10g 

New Feature: Multiple temporary tablespaces could be grouped together and assigned to a user process. This is a new feature in Oracle 10g,and alleviates problems caused by one tablespace being inadequate to hold the results of a sort operation. A tablespace group enables parallel execution servers in a single parallel operation to use multiple temporary tablespaces.

Read-only tablespaces

When data in a tablespace is never updated after the initial load, the data in this tablespace is classified as read-only. These tablespaces could be designated as read-only and are protected from any DML operation. These tablespaces do not need backups on a regular basis. The normal method of periodic backup is when the tablespace is made read-only and immediately when the tablespace is made read/write.

A major use of this feature is in a data warehouse, where the specific range set of data is written to this tablespace and no more will be written to the tablespace. These tablespaces could be converted from read-only to read-write or vice versa.

Undo tablespaces

This is a special type of tablespace introduced in Oracle 9i to basically store undo information. Undo tablespaces cannot contain other object types like TABLES or INDEXES. When an undo tablespace is implemented, it replaces the rollback segment (RBS) concept used in previous versions of Oracle by providing a read-consistent image of the data to manage undo activity. All characteristics of the RBS options are available under the undo tablespace option also. By allowing Oracle to manage the tablespace, undo tablespaces could be maintained either manually or automatically, like the RBS option. The undo tablespace option is invoked by defining the UNDO_MANAGEMENT parameter in the parameter file to either MANUAL or AUTO. The manual option indicates that the tablespace management will be done manually, and the auto option indicates that Oracle will manage this tablespace automatically without any manual intervention.

The undo tablespace option provides the ability to retain the data in the undo tablespace for a predefined amount of time. This is based on the UNDO_RETENTION parameter defined in the parameter file. Setting this parameter to the right value helps in managing the read-consistent image of data for a specific period of time, thus avoiding the ''snapshot too old'' error.

Undo tablespaces are created using the locally managed tablespace option.

Bigfile tablespace

Oracle supports the creation of single file tablespaces called BIGFILE tablespaces, which can be up to 8 million terabytes in size. The advantage of using a large tablespace is to reduce the number of tablespaces per database, thus reducing the maintanance on the tablespaces.

The traditional tablespace are now called SMALLFILE tablespace. The specific type of tablespace could be defined:

1. When the tablespace is created, for example.

   CREATE BIGFILE TABLESPACE emp_data_p001 DATAFILE '/u06/ora_data/RAC1/emp_data_001.dbf' SIZE 1T EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

2. By setting the default tablespace type to either BIGFILE or SMALLFILE when the database is initially created. Once the default tablespace has been defined at the database level, all tablespaces created subsequently are created of this type unless explicitly specified to be of a different type. If no default tablespace type is mentioned the default type is SMALLFILE tablespace.

   CREATE DATABASE proddb SET DEFAULT BIGFILE TABLESPACE UNDO TABLESPACE undotbs DEFAULT TEMPORARY TABLESPACE temptsl;

Due to the large size of these tablespaces, in addition to being able to specify SIZE in kilobytes (K) and megabytes (M) the SIZE can now be specfied in gigabytes (G) or terabytes (T). BIGFILE tablespaces should be created as locally managed with automatic segment space management feature. However, creating BIGFILE temp and undo tablespaces is permitted even though their segments are manually managed.

Oracle 10g 

New Feature: BIGFILE tablespace is a new tablespace type introduced in Oracle 10g to cater to the ultra large databases. Gigabytes and terabytes size definitions are new in Oracle 10g and have been added to help in defining the ultra large tablespaces.

3.3.2 Data file

A tablespace in an Oracle database consists of one or more physical data files. A data file can be associated with only one tablespace and only one database.

Oracle creates a data file for a tablespace by allocating the specified amount of disk space plus the overhead required for the file header. When a data file is created, the operating system under which Oracle runs is responsible for clearing old information and authorizations from a file before allocating it to Oracle. If the file is large, this process can take a significant amount of time. The first tablespace in any database is always the SYSTEM tablespace, so Oracle automatically allocates the first data files of any database to the SYSTEM tablespace during database creation.

The following is a dump of the data file assigned to tablespace SYSTEM and contains the file header information.

DATA FILE #1: (name #233) /dev/vx/rdsk/oraracdg/partition1G_3 creation size=0 block size=8192 status=0xe head=233 tail=233 dup=1 tablespace 0, index=1 krfil=1 prev_file=0 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00 Checkpoint cnt:139 scn: 0x0000.06ffc050 11/20/2002 20:38:14 Stop scn: 0xffff.ffffffff 11/16/2002 19:01:17 Creation Checkpointed at scn: 0x0000.00000006 08/21/2002 17:04:05 thread:0 rba:(0x0.0.0) enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 Offline scn: 0x0000.065acf1d prev_range: 0 Online Checkpointed at scn: 0x0000.065acf1e 10/19/2002 09:43:15 thread:1 rba:(0x1.2.0) enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 Hot Backup end marker scn: 0x0000.00000000 aux_file is NOT DEFINED FILE HEADER:   Software vsn=153092096=0x9200000, Compatibility   Vsn=134217728=0x8000000   Db ID=3598885999=0xd682a46f, Db Name='PRODDB'   Activation ID=0=0x0   Control Seq=2182=0x886, File size=115200=0x1c200   File Number=1, Blksiz=8192, File Type=3 DATA Tablespace #0 - SYSTEM rel_fn:1 Creation at scn: 0x0000.00000006 08/21/2002 17:04:05 Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0 reset logs count:0x1c5a1a33 scn: 0x0000.065acf1e recovered at 11/16/ 2002 19:02:50 status:0x4 root dba:0x004000b3 chkpt cnt: 139 ctl cnt:138 begin-hot-backup file size: 0 Checkpointed at scn: 0x0000.06ffc050 11/20/2002 20:38:14 thread:2 rba:(0x15.31aa6.10) enabled threads: 01100000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 Backup Checkpointed at scn: 0x0000.00000000 thread:0 rba:(0x0.0.0) enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 External cache id: 0x0 0x0 0x0 0x0 Absolute fuzzy scn: 0x0000.00000000 Recovery fuzzy scn: 0x0000.00000000 01/01/1988 00:00:00 Terminal Recovery Stamp scn: 0x0000.00000000 01/01/1988 00:00:00 

The header of each data file contains the DB Id field, which is used to verify that this file is associated with the correct database. The file number is the data file number and the file type of value 3 indicates that it is a data file. (Value 6 would indicate a temp file.)

The tablespace number and names indicate that the data file is assigned to tablespace SYSTEM; the rel_fn indicates the relative file number for the data file.

Some of this information can also be obtained by querying the V$DATAFILE_HEADER data dictionary view.

SELECT FILE#,STATUS,FORMAT,FUZZY,TABLESPACE_NAME,NAME, RFILE# FROM V$DATAFILE_HEADER / FILE# STATUS    FORMAT FUZ TABLESPACE_NAME     ----------------------------------------------- NAME                                               RFILE# -----------------------------------------------     1 ONLINE        8 YES SYSTEM         C:\ORACLE\ORADATA\ORA9IDB\SYSTEM01.DBF              1     2 ONLINE        8 YES UNDOTBS1         C:\ORACLE\ORADATA\ORA9IDB\UNDOTBS01.DBF             2     3 ONLINE        8 YES CWMLITE         C:\ORACLE\ORADATA\ORA9IDB\CWMLITE01.DBF             3     4 ONLINE        8 YES DRSYS         C:\ORACLE\ORADATA\ORA9IDB\DRSYS01.DBF               4 5 ONLINE        8 YES EXAMPLE         C:\ORACLE\ORADATA\ORA9IDB\EXAMPLE01.DBF             5     6 ONLINE        8 YES INDX         C:\ORACLE\ORADATA\ORA9IDB\INDX01.DBF                6     7 ONLINE        8 YES ODM         C:\ORACLE\ORADATA\ORA9IDB\ODM01.DBF                 7     8 ONLINE        8 YES TOOLS         C:\ORACLE\ORADATA\ORA9IDB\TOOLS01.DBF               8     9 ONLINE        8 YES USERS         C:\ORACLE\ORADATA\ORA9IDB\USERS01.DBF               9     10 ONLINE        8 YES XDB         C:\ORACLE\ORADATA\ORA9IDB\XDB01.DBF                 10 10 rows selected                

Column FORMAT indicates the compatibility of the data file structure. In this case, although the output is from an Oracle 9i Release 1 database, the FORMAT column continues to show it is in Version 8. In other words the format has not changed between these releases.

Column FUZZY indicates if the file status is in a fuzzy mode or not. This indicates that the file has encountered certain changes since the last checkpoint operation. The fuzzy bit columns in the view and in the data file dump above play an important role during recovery operations.

If the instances were to crash and a copy of the file was saved, the copy would remain in a fuzzy status up to the time of the next crash recovery. A similar marker is also created after crash recovery so that it is possible to know the extent of fuzziness for backups taken after a crash.

The two fuzzy bits used for recovery purposes are:

• Absolute fuzzy: In server-managed backups, RMAN in particular calculates the absolute fuzziness of the backup. The absolute fuzzy SCN is the greatest SCN of all the blocks that are changed after the checkpoint. The absolute fuzzy flag is cleared when there is a file checkpoint that advances the SCN beyond the absolute fuzzy SCN.

• Recovery fuzzy: This means that the file is in the process of having media recovery applied to it. It is set when media recovery is being applied and is cleared when all the redo logs for that file have been applied.

When a data file is first created, the allocated disk space is formatted but does not contain any user data. However, Oracle reserves the space to hold the data for future segments of the associated tablespace. As the data grows in a tablespace, Oracle uses the free space in the associated data files to allocate extents for the segment.

3.3.3 Segments

A segment is a set of extents allocated for a certain logical database object. All the segments assigned to one object must be in the same tablespace. Extents are allocated to the segments as needed.

There are five types of segments.

Data segment

Every table has a data segment. Data in the tables is stored in the data segment extents. Every table in the database has a data segment in which to store all of its data.

Index segment

Indexes created on the various tables have an index segment that stores data.

Rollback segments

Rollback segments are created for a database for the purpose of temporarily storing undo information. Undo information may be required if the user decides to rollback the transaction and the data has to be restored to the previous state or is required during a recovery operation. Undo information, or the data in these rollback segments, is also required to maintain read consistency.

Information in the rollback segments is stored on a per-transaction basis. Oracle also maintains the order of changes in the rollback segments, which helps Oracle to restore the data to the actual state in case a recovery is required. When a segment is released after use, but is required by another transaction, accessing the rollback data for read consistency could cause an error when the released segment is overwritten with new data. It is advantageous to create many small rollback segments for small transactions and large rollback segments when the transactions or rows retrieved are more in number, for example in a batch processing environment.

Segments comprise one or more extents and an extent comprises one or more data blocks. When a transaction is started, Oracle assigns a rollback segment to the transaction. Other transactions could also use the same rollback segment, and sometimes the same extents. However, only a single transaction could be related to a data block. Oracle uses the extents within a rollback segment in cyclical manner.

Figure 3.8 explains the structure of creation and usage of extents within a rollback segment. A rollback segment has certain initial extents, which are created based on the definition parameters. When a transaction is started, it is assigned to a specific extent within a segment. Many other transactions may also be using the same extent. Extents are assigned in a cyclical manner and are deallocated in the reverse order of their creation.

Figure 3.8: Structure of creation and usage of extents.

During the process of using the allocated extents, and when Oracle determines the assigned extent was not sufficient, Oracle approaches the next extent for the purpose of writing to it. However, if the specific extent has an active transaction, the next extent is approached. Finally, if all the available extents could not be used, Oracle allocates a new extent. Extents are assigned in a cyclical manner. Similarly, when an extent or segment is not used for a certain amount of time, the extents are deallocated in the reverse order in which they were created, or assigned, to the rollback segment. These deallocated extents are available for future allocation when needed.

Very careful attention should be placed on the creation of rollback segments to ensure that the creation of them is to the right size, which is plentiful enough based on the environment (OLTP, batch, etc.). Not giving proper attention to the size of the rollback segments, based on the data size, could cause a ''ORA-22924 snapshot too old'' error.

Undo segment

Very much like the traditional rollback segments, in Oracle 9i a method of managing undo space has been introduced, called undo segments. With the automatic undo management feature, the undo segment management is also dynamic in nature. Unlike the rollback segments, which are created and managed by the DBAs, the undo segments are created and managed by Oracle. These undo segments house the transactions tables and undo blocks.

More details about the undo management, undo segments, etc., will be covered in Chapter 4 (Real Application Cluster Architecture).

Oracle 9i 

New Feature: In Oracle 9i, a new feature is introduced called undo tablespace. With this feature the user has the choice of either using the RBS option or configuring the system to use the undo tablespace. More details on the undo tablespace option can be found in Section 3.3.1.

Temporary segment

A temporary segment is created when a process requires additional temporary space or work area, to complete execution or for large data sorts that cannot be performed in memory. These temporary segment extents are released when the process completes execution. Due to the nature of the segment there is frequent allocation and reallocation of extents based on the need.

3.3.4 Extent management

Dictionary managed

In Oracle, when the initial allocation of blocks has reached its thresh old value, Oracle allocates blocks, called extents, to write data. The extent is a specific number of contiguous data blocks during a single allocation.

When a table is created it allocates an initial extent of a specific number of data blocks. This is performed with the intention that, when actual data is written to disk, the table has already allocated and created space to accept the data. When data is written and the initial extent is full, Oracle allocates further extents either equal to the size of the initial extent or greater than the initial extent as specified during table definition. For allocation of extents, Oracle searches the tablespace for a free contiguous set of data blocks equal to or greater than the initial extent. When this is found, it creates the extent.

Oracle finds free space to make an extent based on the following algorithm:

• It finds the first set of contiguous blocks that matches the exact size of the required extent plus one extra block from the free space, or from the space that was released when rows were deleted from the table. If it finds a set of contiguous blocks, it allocates them as extents.

• If it does not find the contiguous block that is the exact size by searching the reusable space, it looks for a set of contiguous blocks greater than the required extent.

• If Oracle is unable to find a contiguous space greater than the required size, it frees up space by coalescing any adjacent free space from the reusable free space.

• If no free space is available for coalescing, then Oracle has no choice but to resize the tablespace by using the autoextension method.

Oracle has to take numerous steps before adding extents to the tablespace. This search for space to create or add an extent is a routine operation when required but adds cost to performance. For this reason, it is very important to size the extents carefully.

Locally managed

Under the locally managed tablespace option, Oracle creates extents based on an internal algorithm. Extents are created using the following method: the first 16 extents are created, in 64 KB size each. Subsequently, when Oracle needs to create additional extents it changes the algorithm and allocates the next 63 extents of 1 MB size each; the next round again there is variation in the number and size of extents, namely 120 extents of 8 MB size each. After this stage extents are created in equal sizes of 64 MB with no variations until the maximum size of the tablespace/file is reached.

3.3.5 Data block

Oracle manages data storage in the data files as units called data blocks. Data blocks are the smallest unit of I/O used by the database. The data block corresponds to a specific number of bytes of physical database space in a data file. The size of a data block is specified at the time of database or tablespace creation. The data block represented by the DB_BLOCK_SIZE defined during database creation is a multiple of the operating system or disk block sizes.

Figure 3.9 is a dissection of an Oracle data block. A data block comprises the block header and the data layer.

Figure 3.9: Data block dissection.

Block header

The block header comprises the cache layer and the transaction layer.

Cache layer

This is the first layer of the block header and contains structures such as:

• The data block address (DBA)

• The block type

• The block format

• A system change number (SCN)

The cache layer is 20 bytes in size. The block types indicate if the object is a table, index, undo segment, temporary segment, etc. The block format indicates the version format, for example, v8, v9, etc. The SCN number on the cache layer is used for ordering purposes, especially during a recovery operation, to apply records in the order in which they were created.

Transaction layer

This layer has two parts:

• A fixed component contains the transactional block header and one descriptor interested transaction list (ITL). This area contains information about the block/object type, the time of the last block cleanout and the number of ITL entries in the variable portion of the transaction layer, a free list link, and a free space lock.

• The variable portion contains the ITLs necessary for a process to modify rows within the block. By default, Oracle maintains one ITL for a data block containing a table and two ITLs for a data block that contains the index. The default number of ITLs created is influenced by the INITRANS parameter used during table or index creation. The variable portion of the transaction layer will adjust dynamically, expanding as required based on the definitions, provided there is sufficient space available for this purpose. Similarly, the MAXTRANS parameter limits the number of ITLs that can be concurrently allocated and used in the block. If the number of ITLs is not sufficient, then additional entries are allocated dynamically from the free space, provided MAXTRANS is not reached.

Dynamic parameter changes, such as changes to the INITRANS and MAXTRANS values, will not take effect until the next or during subsequent block creations.

Note 

Setting the values of INITRANS and MAXTRANS to really high values causes a considerable space allocation in the transaction layer and hence the space in the data layer is reduced. Reduced data area means that more I/O will be required to read the required data set.

When a process wishes to update one or more rows in a data block, it must be allocated an ITL entry. Each row in the data block contains a one-byte field called the lock byte within its row header. This lock byte indicates which ITL entry is or was interested in updating this row. The ITL entry includes a TXID (transaction ID) and an undo block address (UBA) of the last undo record created to update the block for this current transaction.

The UBA consists of a data block address, sequence number/ incarnation number of the undo block, and a record number within the undo block. This is used to directly access the undo records. Other information is also stored in each ITL entry, including transaction status flags, number of rows locked (Lck), and a commit SCN or free space credit (depending on DML type and commit status).

Data layer

The data layer comprises the table directory and row directory, and free space for new data, row data, and a tail.

Table directory

The table directory indicates where the row directory starts. Its usefulness is more obvious in a cluster where more than one table shares the same data blocks. In order to retrieve the rows belonging to one of the tables in the cluster, it is necessary to know exactly where the rows start and finish.

Row directory

Similar in concept to the table directory, the row directory has a similar application. When a data block is accessed using a ROWID, for example, the Oracle server does not need to scan the entire block. Using the slot number contained in the ROWIDs, the Oracle server can read the appropriate offset in the row directory and proceed to read the exact row. Row offsets do not have any specific method of storage; it could be stored anywhere in the data layer. This implies that rows are stored randomly within the data block.

Free space

The free space is used for insertion of new rows into the table. When Oracle is unable to find space from the released blocks it uses space from the free space to insert rows. (The algorithm used by Oracle to obtain this free space is explained in Section 3.3.4.) The free space is based on certain parameter values specified at the table creation time.

Row data

The data area contains the actual rows of data or indexes. Oracle stores data in this area. When the data area is full, Oracle requests space from the free space area. When a user inserts data, through a DML operation either directly or through an application program, the data is inserted into the data blocks via the data files and the tablespace. The data can subsequently be found in the data area. When data is retrieved by a query also, Oracle retrieves the values from this area.

The various areas and sizes allocated for the various layers of the data block could be obtained by querying the V$TYPE_SIZE view:

SELECT     COMPONENT,     TYPE,     DESCRIPTION,     TYPE_SIZE FROM V$TYPE_SIZE WHERE COMPONENT LIKE 'K%' / COMPONENT    TYPE            DESCRIPTION                 TYPE_SIZE ------------------------------------------------------------------- K            KDBA        DATABASE BLOCK ADDRESS           4 K            KTNO        TABLE NUMBER IN CLUSTER          1 K            KSCN        SYSTEM COMMIT NUMBER             8 K            KXID        TRANSACTION ID                   8 K            KUBA        UNDO ADDRESS                     8 KCB          KCBH        BLOCK COMMON HEADER              20 KTB         KTBIT        TRANSACTION VARIABLE HEADER      24 KTB         KTBBH        TRANSACTION FIXED HEADER         48 KDB          KDBH        DATA HEADER                      14 KDB          KDBT        TABLE DIRECTORY ENTRY            4 KTE         KTECT        EXTENT CONTROL                   44 KTE         KTECH        EXTENT CONTROL                   72 KTE         KTETB        EXTENT TABLE                     8 KTS         KTSHC        SEGMENT HEADER                   8 KTS         KTSFS        SEGMENT FREE SPACE LIST          20 KTU         KTUBH        UNDO HEADER                      16 KTU         KTUXE        UNDO TRANSACTION ENTRY           40 KTU         KTUXC        UNDO TRANSACTION CONTROL         104 KDX         KDXCO        INDEX HEADER                     16 KDX         KDXLE        INDEX LEAF HEADER                32 KDX         KDXBR        INDEX BRANCH HEADER              24 21 rows selected. 

The above query provides the description of the various layers of the various database components. Of the types listed above, the components of type KCB and KTB are related to the data block.

Oracle 9i 

New Feature: The standard block size is specified by the initialization parameter, DB_BLOCK_SIZE. In addition, up to five non-standard block sizes may be specified. The data block sizes should be a multiple of the operating system's block size, within the maximum limit, to avoid unnecessary I/O. Oracle data blocks are the smallest units of storage that Oracle can use or allocate.

---