A segment in Oracle is an object that consumes storage on disk. While there are many segment types, the most popular are as follows:

•\ Cluster: This segment type is capable of storing tables. There are two types of clusters: B*Tree and hash. Clusters are commonly used to store related data from multiple tables prejoined on the same database block and to store related information from a single table together. The name “cluster” refers to this segment’s ability to cluster related information physically together.

•\ Table: A table segment holds data for a database table and is perhaps the most common segment type used in conjunction with an index segment.

•\ Table partition or subpartition: This segment type is used in partitioning and is very similar to a table segment. A table partition or subpartition segment holds just a slice of the data from a table. A partitioned table is made up of one or more table partition segments, and a composite partitioned table is made up of one or more table subpartition segments.

•\ Index: This segment type holds an index structure.

•\ Index partition: Similar to a table partition, this segment type contains some slice of an index. A partitioned index consists of one or more index partition segments.

•\ Lob partition, lob subpartition, lobindex, and lobsegment: The lobindex and lobsegment segments hold the structure of a large object, or LOB. When a table containing a LOB is partitioned, the lobsegment will be partitioned as well—the lob partition segment is used for that. It is interesting to note that there is not a lobindex partition segment type—for whatever reason, Oracle marks the partitioned lob index as an index partition (one wonders why a lobindex is given a special name). LOBs are discussed in full detail in Chapter 12.

•\ Nested table: This is the segment type assigned to nested tables, a special kind of child table in a master/detail relationship that we’ll discuss later.

•\ Rollback and Type2 undo: This is where undo data is stored. Rollback segments are those manually created by the DBA. Type2 undo segments are automatically created and managed by Oracle.

So, for example, a table may be a segment. An index may be a segment. I stress the words “may be” because we can partition an index into separate segments. So, the index object itself would just be a definition, not a physical segment—and the index would be made up of many index partitions, and each index partition would be a segment. A table may be a segment or not. For the same reason, we might have many table segments due to partitioning, or we might create a table in a segment called a cluster. Here the table will reside, perhaps with other tables in the same cluster segment.

The most common case, however, is that a table will be a segment and an index will be a segment. This is the easiest way to think of it for now. When you create a table, you are normally creating a new table segment, and, as discussed in Chapter 3, that segment consists of extents, and extents consist of blocks. This is the normal storage hierarchy. But it is important to note that only the common case has this one-to-one relationship.

For example, consider this simple CREATE TABLE statement:
$ sqlplus eoda/foo@PDB1
SQL> create table t ( x int primary key, y clob, z blob );

This statement creates six segments, assuming Oracle 11g Release 1 and before; in Oracle 11g Release 2 and above, segment creation is deferred until the first row is inserted by default (we’ll use syntax to have the segments created immediately in the following). If you issue this CREATE TABLE statement in a schema that owns nothing, you’ll observe the following:
SQL> select segment_name, segment_type from user_segments;no rows selected
SQL> select segment_name, segment_type from user_segments;
SYS_LOB0000021096C00003$$ LOBSEGMENT
SYS_LOB0000021096C00002$$ LOBSEGMENT
SYS_IL0000021096C00003$$ LOBINDEX
SYS_IL0000021096C00002$$ LOBINDEX
SYS_C005958 INDEX

The table itself created a segment in this example: the first row in the output. Also, the primary key constraint created an index segment in this case in order to enforce uniqueness.

Note A unique or primary key constraint may or may not create a new index. If there is an existing index on the constrained columns, and these columns are on the leading edge of the index, the constraint can and will use them.

Additionally, each of the LOB columns created two segments: one segment to store the actual chunks of data pointed to by the character large object (CLOB) or binary large object (BLOB) pointer and one segment to organize them. LOBs provide support for very large chunks of information, up to many gigabytes in size. They are stored in chunks in the lobsegment, and the lobindex is used to keep track of where the LOB chunks are and the order in which they should be accessed.

Note The deferred segment creation feature is available only in the Enterprise Edition of Oracle. If you work in an environment that has a mixture of Enterprise Edition and Standard Edition databases, then be careful when exporting objects from an EE database to an SE database. If you attempt to export objects that have no segments created or attempt to import into an SE database, you may receive this error: ORA-00439 feature not enabled. One workaround for this is to initially create the tables in the EE database with SEGMENT CREATION IMMEDIATE. See Oracle Support note 1087325.1 for further details.