【IT168 技术文档】Oracle数据库中有多少类型的段，除了常见的TABLE、INDEX之外还有哪些?下面通过Oracle9i的数据字典来探讨Oracle的段类型。
SQL> select distinct segment_type from dba_segments;
SEGMENT_TYPE
------------------
CACHE
CLUSTER
INDEX
INDEX PARTITION
LOBINDEX
LOBSEGMENT
NESTED TABLE
ROLLBACK
TABLE
TABLE PARTITION
TYPE2 UNDO
然而在DBA_SEGMENTS视图中，不一定包含了所有的段类型，我们从DBA_SEGMENTS的定义中去寻找Oracle的段类型。
SQL> select text from dba_views where view_name=’DBA_SEGMENTS’;
TEXT
----------------------------------------------------------------------------------------------------
select owner, segment_name, partition_name, segment_type, tablespace_name,
header_file, header_block,
dbms_space_admin.segment_number_blocks(tablespace_id, relative_fno,
header_block, segment_type_id, buffer_pool_id, segment_flags,
segment_objd, blocks)*blocksize,
dbms_space_admin.segment_number_blocks(tablespace_id, relative_fno,
header_block, segment_type_id, buffer_pool_id, segment_flags,
segment_objd, blocks),
dbms_space_admin.segment_number_extents(tablespace_id, relative_fno,
header_block, segment_type_id, buffer_pool_id, segment_flags,
segment_objd, extents),
initial_extent, next_extent, min_extents, max_extents, pct_increase,
freelists, freelist_groups, relative_fno,
decode(buffer_pool_id, 0, ‘DEFAULT’, 1, ‘KEEP’, 2, ‘RECYCLE’, NULL)
from sys_dba_segs
SQL> select text from dba_views where view_name=’SYS_DBA_SEGS’;
TEXT
--------------------------------------------------------------------------
select u.name, o.name, o.subname,
so.object_type, s.type#,
ts.ts#, ts.name, ts.blocksize,
f.file#, s.block#,
s.blocks * ts.blocksize, s.blocks, s.extents,
s.iniexts * ts.blocksize,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extsize * ts.blocksize),
s.minexts, s.maxexts,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extpct),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(s.lists, 0, 1, s.lists)),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(s.groups, 0, 1, s.groups)),
s.file#, s.cachehint, NVL(s.spare1,0), o.dataobj#
from sys.user$ u, sys.obj$ o, sys.ts$ ts, sys.sys_objects so, sys.seg$ s,
sys.file$ f
where s.file# = so.header_file
and s.block# = so.header_block
and s.ts# = so.ts_number
and s.ts# = ts.ts#
and o.obj# = so.object_id
and o.owner# = u.user#
and s.type# = so.segment_type_id
and o.type# = so.object_type_id
and s.ts# = f.ts#
and s.file# = f.relfile#
select u.name, un.name, NULL,
decode(s.type#, 1, ‘ROLLBACK’, 10, ‘TYPE2 UNDO’), s.type#,
ts.ts#, ts.name, ts.blocksize, f.file#, s.block#,
s.blocks * ts.blocksize, s.blocks, s.extents,
s.iniexts * ts.blocksize, s.extsize * ts.blocksize, s.minexts,
s.maxexts, s.extpct,
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(s.lists, 0, 1, s.lists)),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(s.groups, 0, 1, s.groups)),
s.file#, s.cachehint, NVL(s.spare1,0), un.us#
from sys.user$ u, sys.ts$ ts, sys.undo$ un, sys.seg$ s, sys.file$ f
where s.file# = un.file#
and s.block# = un.block#
and s.ts# = un.ts#
and s.ts# = ts.ts#
and s.user# = u.user#
and s.type# in (1, 10)
and un.status$ != 1
and un.ts# = f.ts#
and un.file# = f.relfile#
union all
select u.name, to_char(f.file#) || ‘.’ || to_char(s.block#), NULL,
decode(s.type#, 2, ‘DEFERRED ROLLBACK’, 3, ‘TEMPORARY’,
4, ‘CACHE’, 9, ‘SPACE HEADER’, ‘UNDEFINED’), s.type#,
ts.ts#, ts.name, ts.blocksize,
f.file#, s.block#,
s.blocks * ts.blocksize, s.blocks, s.extents,
s.iniexts * ts.blocksize,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extsize * ts.blocksize),
s.minexts, s.maxexts,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extpct),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(s.lists, 0, 1, s.lists)),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(s.groups, 0, 1, s.groups)),
s.file#, s.cachehint, NVL(s.spare1,0), s.hwmincr
from sys.user$ u, sys.ts$ ts, sys.seg$ s, sys.file$ f
where s.ts# = ts.ts#
and s.user# = u.user#
and s.type# not in (1, 5, 6, 8, 10)
and s.ts# = f.ts#
and s.file# = f.relfile#
SQL> select text from dba_views where view_name=’SYS_OBJECTS’;
TEXT
--------------------------------------------------------------------------------------
select decode(bitand(t.property, 8192), 8192, ‘NESTED TABLE’, ‘TABLE’), 2, 5,
t.obj#, t.file#, t.block#, t.ts#
from sys.tab$ t
where bitand(t.property, 1024) = 0 /* exclude clustered tables */
union all
select ‘TABLE PARTITION’, 19, 5,
tp.obj#, tp.file#, tp.block#, tp.ts#
from sys.tabpart$ tp
union all
select ‘CLUSTER’, 3, 5,
c.obj#, c.file#, c.block#, c.ts#
from sys.clu$ c
union all
select decode(i.type#, 8, ‘LOBINDEX’, ‘INDEX’), 1, 6,
i.obj#, i.file#, i.block#, i.ts#
from sys.ind$ i
where i.type# in (1, 2, 3, 4, 6, 7, 8, 9)
union all
select ‘INDEX PARTITION’, 20, 6,
ip.obj#, ip.file#, ip.block#, ip.ts#
from sys.indpart$ ip
union all
select ‘LOBSEGMENT’, 21, 8,
l.lobj#, l.file#, l.block#, l.ts#
from sys.lob$ l
union all
select ‘TABLE SUBPARTITION’, 34, 5,
tsp.obj#, tsp.file#, tsp.block#, tsp.ts#
from sys.tabsubpart$ tsp
union all
select ‘INDEX SUBPARTITION’, 35, 6,
isp.obj#, isp.file#, isp.block#, isp.ts#
from sys.indsubpart$ isp
union all
select decode(lf.fragtype$, ‘P’, ‘LOB PARTITION’, ‘LOB SUBPARTITION’),
decode(lf.fragtype$, ‘P’, 40, 41), 8,
lf.fragobj#, lf.file#, lf.block#, lf.ts#
from sys.lobfrag$ lf
因此，从以上几个视图的定义中可以看到，Oracle9i中有如下的段类型：
NESTED TABLE
TABLE
TABLE PARTITION
CLUSTER
LOBINDEX
INDEX
INDEX PARTITION
LOBSEGMENT
TABLE SUBPARTITION
INDEX SUBPARTITION
LOB PARTITION
LOB SUBPARTITION
ROLLBACK
TYPE2 UNDO
DEFERRED ROLLBACK
TEMPORARY
CACHE
SPACE HEADER
UNDEFINED
