Database Reference
In-Depth Information
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.
■
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.
Note
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 that on line 5 of the
CREATE TABLE
statement I used syntax specific to Oracle 11
g
Release 2 and above—the
SEGMENT CREATION IMMEDIATE
clause. If you attempt to use that syntax in earlier releases you will receive:
ops$tkyte%ORA11GR1> Create table t
2 ( x int primary key,
3 y clob,
4 z blob )
5 SEGMENT CREATION IMMEDIATE
6 /
SEGMENT CREATION IMMEDIATE
*
ERROR at line 5:
ORA-00922: missing or invalid option
■
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 MOs note 1087325.1 for
further details.
Note
Segment Space Management
Starting with Oracle9
i
, there are two methods for managing space in segments:
•
Manual Segment Space Management
: You set various parameters such as
FREELISTS
,
FREELIST GROUPS
,
PCTUSED
, and others to control how space is allocated, used, and reused in
a segment over time. I will refer to this space management method in this chapter as
MSSM
,
but bear in mind that that is a made-up abbreviation that you will not find widely in the Oracle
documentation.
•
Automatic Segment Space Management (ASSM)
: You control one parameter relating to how
space is used:
PCTFREE
. The others are accepted when the segment is created, but they are
ignored.
