Oracle Segments Role

Data blocks are used by Oracle in all I/O operations. A segment is composed of one or more extents, but all the data in a table or an index must be contained within a single segment. Database objects, such as tables and indexes, are held in specific segments. You do not specifically create segments since they are automatically created to support different types of storage.

Question: What role do extents play within the context of data blocks and segments in Oracle?
In the Oracle Database Management System, extents play a crucial role in the organization and management of data storage. They serve as an intermediate layer between data blocks and segments, providing a logical and scalable approach to allocating space for database objects such as tables and indexes. Understanding the relationship between data blocks, extents, and segments is essential for database administrators and developers working with Oracle systems.

An extent is a contiguous set of data blocks allocated for a specific database object, such as a table or an index. When a database object requires additional storage, the Oracle DBMS allocates a new extent to accommodate the growth. Each extent is associated with a single segment and belongs to a specific tablespace. Key characteristics of extents include:

1. Allocation: Extents are allocated to database objects as needed, based on the storage parameters defined for the object, such as INITIAL, NEXT, PCTINCREASE, and MINEXTENTS. These parameters determine the size and growth pattern of extents for the object.
2. De-allocation: When a database object is dropped or truncated, the associated extents are de-allocated and returned to the tablespace's free space, making them available for future allocation to other objects.

Extents comprise a contiguous set of data blocks, with all blocks in an extent sharing the same size, as determined by the database block size parameter (DB_BLOCK_SIZE) set during database creation. Data blocks within an extent store the actual data for the associated database object, such as rows in a table or entries in an index.

A segment is a higher-level logical storage structure that represents a database object, such as a table, index, or partition. Segments consist of one or more extents, with each extent contributing to the storage of the object. As a database object grows, new extents are allocated to the corresponding segment, ensuring the object has sufficient space to store its data.
Segments have the following key characteristics:

1. Types: The primary types of segments in Oracle are data segments, index segments, rollback segments, and temporary segments. Each type serves a specific purpose and is associated with a particular kind of database object or operation.
2. Management: Segments are managed at the tablespace level. Each tablespace consists of one or more data files, which store the extents and segments for the database objects within the tablespace.

In summary, extents play a critical role in the organization and management of storage in the Oracle Database Management System. They serve as an intermediate layer between data blocks and segments, providing a scalable and logical approach to allocating space for database objects. Extents are made up of contiguous data blocks, and multiple extents are combined to form segments that represent database objects such as tables and indexes. This hierarchical storage structure enables efficient and flexible space management within Oracle databases.

There are four types of segments used by Oracle:

Above extents, the level of logical database storage is a segment. A segment is a set of extents allocated for a table, index, rollback segment, or for temporary use by a session, transaction, or SQL parser. In relation to physical database structures, all extents belonging to a segment exist in the same tablespace, but they may be in different data files. When the extents of a segment are full, Oracle Database dynamically 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 next lesson is about extents.

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.

A single data segment in an Oracle Database 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

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.

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.

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.