oracle的dba_ segment,查看oracle所有的segment_type

select distinct segment_type from dba_segments可以得到当前所有使用的segment_type

系统中还有哪些没有被使用的segment_type

通过select text from dba_views where view_name = 'DBA_SEGMENTS'可以得出dba_segments的视图定义

select owner, segment_name, partition_name, segment_type, tablespace_name,

header_file, header_block,

decode(bitand(segment_flags, 131072), 131072, blocks,

(decode(bitand(segment_flags,1),1,

dbms_space_admin.segment_number_blocks(tablespace_id, relative_fno,

header_block, segment_type_id, buffer_pool_id, segment_flags,

segment_objd, blocks), blocks)))*blocksize,

decode(bitand(segment_flags, 131072), 131072, blocks,

(decode(bitand(segment_flags,1),1,

dbms_space_admin.segment_number_blocks(tablespace_id, relative_fno,

header_block, segment_type_id, buffer_pool_id, segment_flags,

segment_objd, blocks), blocks))),

decode(bitand(segment_flags, 131072), 131072, extents,

(decode(bitand(segment_flags,1),1,

dbms_space_admin.segment_number_extents(tablespace_id, relative_fno,

header_block, segment_type_id, buffer_pool_id, segment_flags,

segment_objd, extents) , 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

sys_dba_segs又是什么

通过select object_type from dba_objects where object_name = upper('sys_dba_segs')可以得出sys_dba_segs是一个视图

继续查询

select text from dba_views where view_name = upper('sys_dba_segs')可以得到

select NVL(u.name, 'SYS'), 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#

union all

select NVL(u.name, 'SYS'), 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 NVL(u.name, 'SYS'), 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#

即segment_type包括包括ROLLBACK,TYPE2 UNDO,DEFERRED ROLLBACK,TEMPORARY,CACHE,SPACE HEADER,UNDEFINED和sys_objects里面的object_type

通过select distinct object_type from sys_objects可以得到sys_objects里面所有的object_type，在此不一一列举

其实sys_objects也是一个view,在dba_views可以获知其定义

注意测试环境oracle database 10.2.0.1

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20876463/viewspace-610933/，如需转载，请注明出处，否则将追究法律责任。