oracle headerblock,【学习笔记】Oracle 通过基表获取segment header block的方法案例

天萃荷净

通过基表获取segment header block，数据库不能open的时候,可以通过dul挖取相关基表(user$,obj$,ts$,tab$,seg$,file$),从而来获得segment header信息,然后通过dump该block,结合shell脚本获得extents分布脚本来获得extent分布

SELECT NVL (u.name, 'SYS'),

o.name,

o.subname,

so.object_type,

s.type#,

DECODE (BITAND (s.spare1, 2097408),

2097152, 'SECUREFILE',

256, 'ASSM',

'MSSM'),

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,

DECODE (BITAND (s.spare1, 4194304), 4194304, bitmapranges, NULL),

TO_CHAR (

DECODE (

BITAND (s.spare1, 2097152),

2097152, DECODE (s.lists,

0, 'NONE',

1, 'AUTO',

2, 'MIN',

3, 'MAX',

4, 'DEFAULT',

'INVALID'),

NULL)),

DECODE (BITAND (s.spare1, 2097152), 2097152, s.groups, NULL),

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#,

BITAND (s.cachehint, 3),

BITAND (s.cachehint, 12) / 4,

BITAND (s.cachehint, 48) / 16,

NVL (s.spare1, 0),

o.dataobj#

FROM chf.user$ u,

chf.obj$ o,

chf.ts$ ts,

( SELECT DECODE (BITAND (t.property, 8192), 8192, 'NESTED TABLE', 'TABLE') OBJECT_TYPE,

2 OBJECT_TYPE_ID,

5 SEGMENT_TYPE_ID,

t.obj# OBJECT_ID,

t.file# HEADER_FILE,

t.block# HEADER_BLOCK,

t.ts# TS_NUMBER

FROM chf.tab$ t) so,

chf.seg$ s,

chf.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#

and o.name in('oracleplus','T_oracleplus');

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle 通过基表获取segment header block的方法案例