oracle类型介绍,Oracle数据库的段类型详解

【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