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

 

Trackback

no comment untill now

Add your comment now