Oracle Segments Explained

In a locally managed tablespace, there are two methods that Oracle Database can use to manage segment space: automatic and manual. Manual segment space management uses linked lists called "freelists" to manage free space in the segment, while automatic segment space management uses bitmaps. Automatic segment space management is the more efficient method, and is the default for all new permanent, locally managed tablespaces. Segments in Oracle A segment is a container for objects (such as tables, indexes....). A segment consists of extents. Segment types There are 11 types of segments in Oracle 10g: table table partition index index partition cluster rollback deferred rollback temporary cache lobsegment lobindex These types can be grouped into four segment classes: data segments: table and cluster index rollback temporary data In order to find out the size of the respective segments, use dba_segments (or user_segments, or all_segments). A segment can either be created so that it can have an unlimited or limited number of extents. Oracle keeps track of free blocks for each data segment. Segment Headers The segment header is stored in the first block of the first extent. It contains: The extents table The free lists descriptors The high water mark Temporary segments The following SQL statements require a temporary segment if they cannot be completed in memory: create index. select order by select distinct select group by select union select intersect select minus by analyze table unindexed joins certain correlated subqueries Actually, most of these statements require a sort. Temporary segments can be stored in any tablespace. However, it makes more sense to store temporary segments in a temporary tablespace. SMON frees temporary segments.
Data Segments: Logical Storage Structure: Logical Database Structure: Index Segments: Rollback Segment: