Contact Premier Online Trainings Today

Oracle DBA - Data Blocks, Extents and Segments

Date Added: 24 Feb.2017 Date Updated: 03 Mar.2019 Oracle DBA Full Blog

 

Introduction to Data Blocks, Extents, and Segments

Oracle allocates logical database space for all data in a database. The units of database space allocation are data blocks, extents, and segments.

 

block1

 

At the finest level of granularity, Oracle stores data in data blocks (also called logical blocksOracle 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.

The level of logical database storage greater than an extent is called a segment. 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 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. If the table or index is partitioned, each partition is stored in its own segment.

Oracle allocates space for segments in units of one extent. 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 tablespace. Within a tablespace, a segment can include extents from more than one file; that is, the segment can span datafiles. However, each extent can contain data from only one datafile.

Although you can allocate additional extents, the blocks themselves are allocated separately. If you allocate an extent to a specific instance, the blocks are immediately allocated to the free list. However, if the extent is not allocated to a specific instance, 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.

 

Overview of Data Blocks

Oracle manages the storage space in the datafiles of a database in units called data blocks. A data block is the smallest unit of data used by a database. In contrast, at the physical, operating system level, all data is stored in bytes. Each operating system has a block size. Oracle requests data in multiples of Oracle data blocks, not operating system blocks.

The standard block size is specified by the DB_BLOCK_SIZE initialization parameter. In addition, you can specify of up to five nonstandard block sizes. 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.

Data Block Format

The Oracle data block format is similar regardless of whether the data block contains table, index, or clustered data

 

DBblock2

 

a) Header: contains generic information like block address and type of segment (index, data..)
b) Table Directory : contains information about table having rows in that block
c) Row Directory : contains information about actual row contained in that block
d) Free Space : available space in data block for additional row or update of row which require more space.
e) Row Data : contains table or index data.First three component of data block (Header, Table & Row directory) collectively known as Overhead

 

Overview of Extents

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 datails 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. 

Overview of Segments

A segment is a set of extents that contains all the data for a specific logical storage structure within a tablespace. For example, for each table, Oracle Database allocates one or more extents to form that table's data segment, and for each index, Oracle Database allocates one or more extents to form its index segment.

This section contains the following topics:

·         Introduction to Data Segments
 
·         Introduction to Index Segments
 
·         Introduction to Temporary Segments
 
·         Introduction to Undo Segments and Automatic Undo Management

Introduction to Data Segments

A single data segment in an Oracle Database database holds all of the data for one of the following:

·         A table that is not partitioned or clustered

·         A partition of a partitioned table

·         A cluster of tables

Oracle Database creates this data segment when you create the table or cluster with the CREATE statement.

The storage parameters for a table or cluster determine how its data segment's extents are allocated. You can set these storage parameters directly with the appropriate CREATE or ALTER statement. These storage parameters affect the efficiency of data retrieval and storage for the data segment associated with the object

Introduction to Index Segments

Every nonpartitioned 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.

Oracle Database creates the index segment for an index or an index partition when you issue the CREATE INDEX statement. In this statement, you can specify storage parameters for the extents of the index segment and a 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.

Introduction to Temporary Segments

When processing queries, Oracle Database often requires temporary workspace for intermediate stages of SQL statement parsing and execution. Oracle Database automatically allocates this disk space called a temporary segment. Typically, Oracle Database requires a temporary segment as a database area for sorting. Oracle Database does not create a segment if the sorting operation can be done in memory or if Oracle Database finds some other way to perform the operation using indexes.

Introduction to Undo Segments and Automatic Undo Management

Oracle Database maintains information to reverse changes made to the database. This information consists of records of the actions of transactions, collectively known as undo. Undo is stored in undo segments in an undo tablespace. Oracle Database uses undo information to do the following:

·         Rollback an active transaction

·         Recover a terminated transaction

·         Provide read consistency

·         Recovery from logical corruptions

When a ROLLBACK statement is issued, undo records are used to undo changes that were made to the database by the uncommitted transaction. During database recovery, undo records are used to undo any uncommitted changes applied from the redo log to the datafiles. Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it.

Oracle Database provides a fully automated mechanism, referred to as automatic undo management, for managing undo information and space. In this management mode, for all current sessions, the server automatically manages undo segments and space in the undo tablespace.

Automatic undo management eliminates the complexities of managing rollback segment space. In addition, the system automatically tunes itself to provide the best possible retention of undo information to satisfy long-running queries that may require this undo information. Automatic undo management is the default for new installations of Oracle Database. The installation process automatically creates an undo tablespace.