Learn Oracle

You are just 10 steps away from becoming a Oracle DBA. Materialize your dream by following the The 10 Simple Steps .

Segments Overview

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 allocates one or more extents to form that table's data segment, and for each index, Oracle allocates one or more extents to form its index segment. Oracle databases use four types of segments, which are described in the following sections:

Introduction to Data Segments
Introduction to Index Segments
Introduction to Temporary Segments
Introduction to Rollback Segments

Learn Oracle - Introduction to Data Segments

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

Learn Oracle - 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 creates the index segment for an index or an index partition when Oracle DBA issue the CREATE INDEX statement. In this statement, Oracle DBA 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.

Learn Oracle - Introduction to Temporary Segments

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

Operations that Require Temporary Segments

The following statements could require the use of a temporary segment:

CREATE INDEX
SELECT ... ORDER BY
SELECT DISTINCT ...
SELECT ... GROUP BY
SELECT ... UNION
SELECT ... INTERSECT
SELECT ... MINUS

Some unindexed joins and correlated subqueries can also require use of a temporary segment. For example, if a query contains a DISTINCT clause, a GROUP BY, and an ORDER BY, Oracle can require as many as two temporary segments. If applications often issue statements in the previous list, the database administrator can improve performance by adjusting the initialization parameter SORT_AREA_SIZE.

Segments in Temporary Tables and Their Indexes

Oracle can also allocate temporary segments for temporary tables and indexes created on temporary tables. Temporary tables hold data that exists only for the duration of a transaction or session.

How Temporary Segments Are Allocated

Oracle allocates temporary segments differently for queries and temporary tables.

Allocation of Temporary Segments for Queries

Oracle allocates temporary segments as needed during a user session, in the temporary tablespace of the user issuing the statement. Specify this tablespace with a CREATE USER or an ALTER USER statement using the TEMPORARY TABLESPACE clause. If no temporary tablespace has been defined for the user, the default temporary tablespace is the SYSTEM tablespace. The default storage characteristics of the containing tablespace determine those of the extents of the temporary segment.

Oracle drops temporary segments when the statement completes.

Because allocation and deallocation of temporary segments occur frequently, it is reasonable to create a special tablespace for temporary segments. By doing so, Oracle DBA can distribute I/O across disk devices, and Oracle DBA can avoid fragmentation of the SYSTEM and other tablespaces that otherwise hold temporary segments.

Entries for changes to temporary segments used for sort operations are not stored in the redo log, except for space management operations on the temporary segment.

Allocation of Temporary Segments for Temporary Tables and Indexes

Oracle allocates segments for a temporary table when the first INSERT into that table is issued. (This can be an insert operation internally issued by CREATE TABLE AS SELECT.) The first INSERT into a temporary table allocates the segments for the table and its indexes, creates the root page for the indexes, and allocates any LOB segments.

Segments for a temporary table are allocated in the temporary tablespace of the user who created the temporary table.

Oracle drops segments for a transaction-specific temporary table at the end of the transaction and drops segments for a session-specific temporary table at the end of the session. If other transactions or sessions share the use of that temporary table, the segments containing their data remain in the table.

LEARN ORACLE - BLOGS