Introduction to Data Blocks, Extents and Segments –Logical Storage Structures in Oracle 10g Server

Datablocks, Extents and Segments are units of logical database space allocated by Oracle. At the lowest level of granularity, data is stored by Oracle in Datablocks.

One datablock is equivalent to a specific number of bytes of physical database space on a disk. At the next level of logical database storage is extent which corresponds to a specific number of contiguous data blocks. A segment is a set of extents. Space is allocated in a segment in units of extents. When existing extents are full, extra extents are allocated by Oracle as and when required. So extents need not be contiguous on the disk.

When a database object is created it is assigned a tablespace by default or by specification. A segment is created in that tablespace to hold data associated with that object. Tablespaces are multiple logical sections defined at the time of database creation. When tablespaces are created, datafiles are also created to hold data. A segment and all its extents are stored within one tablespace. A segment can include extents from more than one datafile, but each extent can have data from only one datafile.

A datablock thus is the smallest unit of data in a database. It has a format consisting of a Header, Table directory, Row Directory, Free Space and Row Data. The Header contains information pertaining to block such as address and the type of segment. The Table Directory contains information about the corresponding table. The Row Directory contains information about the rows. The Row Data contains table or index data. Free Space is used when insertion of new rows or updates to existing rows requires extra space.

An extent is a specified number of blocks. When the table is created Oracle allocates an initial extent to the table’s data segment. When this becomes full, Oracle automatically allocates an incremental extent. Tablespaces can be dictionary managed or locally managed. Dictionary Managed tablespaces rely on data dictionary tables to manage space utilization whereas locally managed tablespaces use bitmaps to track space utilization. Depending on whether they are locally managed or dictionary managed oracle uses different algorithms to allocate extents.

A segment is a set of extents that has the data corresponding to a specified logical structure in a tablespace. For example each table may be allocated one or more extents to form the data segment and each index one or more extents to form its index segment.

Data Segments, Index segments, Temporary Segments, Rollback Segments are normally the types of segments used by Oracle. The data segment is created when a table or cluster is created. The index segment for an index or an index partition is created by the create command for index. The temporary segment is allocated as a temporary workspace during query processing. The Rollback segments are allocated when a transaction in a database needs to be undone or during system failure. Rollback segments play an important role in maintaining read consistency and in system recovery.

The space used to store data in Oracle database is controlled by these logical structures. They give an indication of the way the physical space of a database is used.

| Characterization of Materialized views and its types in Oracle10g | Introduction to Capability Maturity Model (CMM) | Introduction to Data Blocks, Extents and Segments –Logical Storage Structures in Oracle 10g Server | Oracle Application Server10g – Business Intelligence Services | Oracle Application Server10g – Content Management Services | Overview of Commit and Rollback – The Transaction Control Statements in Oracle 10g | Overview of Flashback Features in Oracle10g | Overview on Database Change Notification in Oracle10g | Overview on Implicit and Explicit Cursors in PL/SQL | Overview on Sequential Control and Conditional Control Statements in PL/SQL | Overview on some of the new features in Oracle Database 10g | Overview on the usage of Cursor Variables in PL/SQL | Performance Tuning in Oracle10g – Best Practices in Application Development | Representing Conditional Expressions as Data in Oracle10g | Understanding Indexes and their key benefits in Oracle10g | Understanding Oracle10g Application Server –Portal Services |