Database Reference
In-Depth Information
•
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:
EODA@ORA12CR1> create table t ( x int primary key, y clob, z blob );
This statement creates six segments, assuming Oracle 11
g
Release 1 and before; in Oracle 11
g
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 below). If you issue this
CREATE TABLE
statement in a schema that owns nothing, you'll observe
the following:
EODA@ORA12CR1> select segment_name, segment_type from user_segments;
no rows selected
EODA@ORA12CR1> create table t
2 ( x int primary key,
3 y clob,
4 z blob )
5 SEGMENT CREATION IMMEDIATE
6 /
Table created.
EODA@ORA12CR1> select segment_name, segment_type from user_segments;
SEGMENT_NAME SEGMENT_TYPE
------------------------------ ---------------
T TABLE
SYS_LOB0000021096C00003$$ LOBSEGMENT
SYS_LOB0000021096C00002$$ LOBSEGMENT
SYS_IL0000021096C00003$$ LOBINDEX
SYS_IL0000021096C00002$$ LOBINDEX
SYS_C005958 INDEX
6 rows selected.
