Managing DB Objects   «Prev  Next»
Lesson 4 Oracle Segments
Objective What is the role of segments in Oracle?

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--they are automatically created to support different types of storage.

Types of segments

There are four types of segments used by Oracle:
Data segment: A data segment is created each time you create a table. The number and size of extents for a data segment is specified in the CREATE TABLE statement.
Index segment: An index segment is created each time you create an index. The number and size of extents for an index segment is specified in the CREATE INDEX statement.
Temporary segment: A temporary segment may be necessary to provide temporary storage for database operations, such as sorting. Temporary segments are allocated for the temporary tablespace of users who require the additional space for temporary storage.
Rollback segment: A rollback segment contains information needed by your Oracle database to roll back transactions, if necessary. Rollback segments are allocated to the database and cannot be directly accessed by users or database administrators.


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.

Data Segments

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.

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.

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.