无法理解的多出来的几个BUFFER HEADER来自何方，请指教！
本帖最后由 timothyzhn 于 2014-4-6 23:48 编辑环境：
SYS@PROD>select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
过程：
建一张表并插入一条记录，然后获取该记录的文件号、块号
SYS@PROD>create table zn.t3(x int, y int);
Table created.
SYS@PROD>insert into zn.t3 values(1,2);
1 row created.
SYS@PROD>commit;
Commit complete.
SYS@PROD>select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# from zn.t3 where x=1;
FILE# BLOCK#
---------- ----------
4 213
SYS@PROD>select obj#,dataobj# from obj$ where name='T3';
OBJ# DATAOBJ#
---------- ----------
13506 13506
然后把BUFFER CACHE的BH转出出来看一下：
SYS@PROD>ALTER SESSION SET EVENTS 'immediate trace name buffers level 1';
Session altered.
SYS@PROD>select * from v$diag_info where name='Default Trace File';
INST_ID NAME VALUE
---------- ---------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 Default Trace File /s01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_2561.trc
vi /s01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_2561.trc
在trace file中搜索"objn: 13506"，即打算把T3表相关的块信息DUMP出来观察一下。
在查看之前，个人认为应该只会有两个BH，一个是T3表的SEGMENT HEADER对应的BH，一个是T3表中X=1的记录所在块的BH。
但事实是，搜索"objn: 13506"，得到多达8个BH，它们的块号分别不同，按照class分类看了一下：
class: 1--5个data block，其中rdba: 0x010000d5 (4/213)对应的就是记录X=1的BH，其余4个是从哪里来的？
class: 4--1个segment header，这个应该是T3的段头的BH
class: 8--1个1st level bmb
class: 9--1个2nd level bmb
问题就是，我认为应该只有2个BH，那多出来的6个来自哪里？我试图根据其中的文件号、块号构建ROWID到T3表中查询，也一无所获，是否还有其他途径能够得知多出来你的6个BH来自何方？
全部8个BH的DUMP信息列在下方，以及后面尝试追溯的脚本，请指教原因在哪里，还是我的方法有问题？如果太过初级，请提醒我关闭问题，多谢！
汇总一下：
class: 1--5个data block
BH (0x747eeca8) file#: 4 rdba: 0x010000d3 (4/211) class: 1 ba: 0x74668000
set: 11 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 120,28
dbwrid: 0 obj: 13506 objn: 13506 tsn: 4 afn: 4 hint: f
hash: lru:
obj-flags: object_ckpt_list
ckptq: fileq: objq:
st: XCURRENT md: NULL tch: 2
flags: buffer_dirty redo_since_read
LRBA: LSCN: HSCN: HSUB:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x747fa128) file#: 4 rdba: 0x010000d4 (4/212) class: 1 ba: 0x74798000
set: 12 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 110,28
dbwrid: 0 obj: 13506 objn: 13506 tsn: 4 afn: 4 hint: f
hash: lru:
obj-flags: object_ckpt_list
ckptq: fileq: objq:
st: XCURRENT md: NULL tch: 2
flags: buffer_dirty redo_since_read
LRBA: LSCN: HSCN: HSUB:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x747dbca8) file#: 4 rdba: 0x010000d5 (4/213) class: 1 ba: 0x74468000
set: 9 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 115,28
dbwrid: 0 obj: 13506 objn: 13506 tsn: 4 afn: 4 hint: f
hash: lru:
obj-flags: object_ckpt_list
ckptq: fileq: objq:
st: XCURRENT md: NULL tch: 2
flags: buffer_dirty redo_since_read
LRBA: LSCN: HSCN: HSUB:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x747e61b8) file#: 4 rdba: 0x010000d6 (4/214) class: 1 ba: 0x7457e000
set: 10 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 131,19
dbwrid: 0 obj: 13506 objn: 13506 tsn: 4 afn: 4 hint: f
hash: lru:
obj-flags: object_ckpt_list
ckptq: fileq: objq:
st: XCURRENT md: NULL tch: 2
flags: buffer_dirty redo_since_read
LRBA: LSCN: HSCN: HSUB:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x747eeb78) file#: 4 rdba: 0x010000d7 (4/215) class: 1 ba: 0x74666000
set: 11 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 120,28
dbwrid: 0 obj: 13506 objn: 13506 tsn: 4 afn: 4 hint: f
hash: lru:
obj-flags: object_ckpt_list
ckptq: fileq: objq:
st: XCURRENT md: NULL tch: 2
flags: buffer_dirty redo_since_read
LRBA: LSCN: HSCN: HSUB:
cr pin refcnt: 0 sh pin refcnt: 0
class: 4--segment header，这个应该是T3的段头
BH (0x747fa388) file#: 4 rdba: 0x010000d2 (4/210) class: 4 ba: 0x7479c000
set: 12 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 110,28
dbwrid: 0 obj: 13506 objn: 13506 tsn: 4 afn: 4 hint: f
hash: lru:
obj-flags: object_ckpt_list
ckptq: fileq: objq:
st: XCURRENT md: NULL tch: 2
flags: buffer_dirty redo_since_read
LRBA: LSCN: HSCN: HSUB:
cr pin refcnt: 0 sh pin refcnt: 0
class: 8--1st level bmb
BH (0x747e62e8) file#: 4 rdba: 0x010000d0 (4/208) class: 8 ba: 0x74580000
set: 10 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 131,19
dbwrid: 0 obj: 13506 objn: 13506 tsn: 4 afn: 4 hint: f
hash: lru:
obj-flags: object_ckpt_list
ckptq: fileq: objq:
st: XCURRENT md: NULL tch: 1
flags: buffer_dirty redo_since_read
LRBA: LSCN: HSCN: HSUB:
cr pin refcnt: 0 sh pin refcnt: 0
class: 9--2nd level bmb
BH (0x747dbdd8) file#: 4 rdba: 0x010000d1 (4/209) class: 9 ba: 0x7446a000
set: 9 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 115,28
dbwrid: 0 obj: 13506 objn: 13506 tsn: 4 afn: 4 hint: f
hash: lru:
obj-flags: object_ckpt_list
ckptq: fileq: objq:
st: XCURRENT md: NULL tch: 1
flags: buffer_dirty redo_since_read
LRBA: LSCN: HSCN: HSUB:
cr pin refcnt: 0 sh pin refcnt: 0
我想弄清楚的是为什么同一个objn: 13506，其中class=1的就有多达5个BH，而表T3中的记录只有一条。且对应的文件号相同块号不同，除了块号213的是T3表的X=1的记录外，其他四个来自哪里？
SYS@PROD>select addr, indx, hladdr, flag,tch,TS#, FILE# ,DBABLK ,state,obj,ba from x$bh where file#=4 and dbablk between 211 and 215 order by dbablk;
ADDR INDX HLADDR FLAG TCH TS# FILE# DBABLK STATE OBJ BA
---------------- ---------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------------
00007FF7DAA09B80 3958 000000008412E908 35651584 2 4 4 211 1 13506 0000000074668000
00007FF7DAA09B80 237 0000000084FF1EC8 35651584 2 4 4 212 1 13506 0000000074798000
00007FF7DAA09B80 6247 00000000841D5C80 35651584 2 4 4 213 1 13506 0000000074468000
00007FF7DAA09B80 2541 00000000840C76A0 35651584 2 4 4 214 1 13506 000000007457E000
00007FF7DAA09B80 8527 000000008427EFF8 35651584 2 4 4 215 1 13506 0000000074666000
SYS@PROD>select name from obj$ where obj#=13506;
NAME
------------------------------
T3
select DBMS_ROWID.ROWID_CREATE (1-生成rowid,13506-对象号,4-文件号,213-块号,0-行号) from dual;
SYS@PROD>select DBMS_ROWID.ROWID_CREATE (1,13506,4,213,0) from dual;
DBMS_ROWID.ROWID_C
------------------
AAADTCAAEAAAADVAAA
SYS@PROD>select * from zn.t3 where rowid='AAADTCAAEAAAADVAAA';
X Y
---------- ----------
1 2
SYS@PROD>select DBMS_ROWID.ROWID_CREATE (1,13506,4,211,0) from dual;
DBMS_ROWID.ROWID_C
------------------
AAADTCAAEAAAADTAAA
SYS@PROD>select * from zn.t3 where rowid='AAADTCAAEAAAADTAAA';
no rows selected
还是没弄明白多出来的BH是哪儿来的？ 我把其中一个data block 212 dump出来看：
Block header dump: 0x010000d4
Object id on Block? Y
seg/obj: 0x34c2 csc: 0x00.7581d itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x10000d0 ver: 0x01 opc: 0
inc: 0 exflg: 0
发现block header上已经有了OBJECT ID:
Object id on Block? Y
seg/obj: 0x34c2--十进制就是13506，就是T3表，与213块上的OBJECT ID相同
但是下面的nrow=0，说明块是空的，还没有写数据进去，但是块头已经被写入object id了。
T3所在表空间的属性如下：
TABLESPACE_NAME BLOCK_SIZE EXTENT_MAN SEGMEN
------------------------------ ---------- ---------- ------
EXAMPLE 8192 LOCAL AUTO
猜想这可能是在建表或插入第一条记录的时候就在若干个块头上写入了表的OBJECT ID，包括空白的块，留待以后写入记录的时候用。至于这与ORACLE的存储空间管理方式有什么关系，这样提前写入OBJECT ID的方式有什么好处，我再自学一下。 问题关闭，留待自学，叨扰给位大侠了！多谢关注！ 本帖最后由 timothyzhn 于 2014-4-8 14:22 编辑
重新总结了一下，怀疑跟段空间管理方式有关。
一、在SEGMENT SPACE MANAGEMENT=MANUAL的SYSTEM表空间下建表：
1、建表sys.t4
SYS@PROD>create table t4(x int, y int);
Table created.
SYS@PROD>select tablespace_name,block_size,extent_management,segment_space_management from dba_tablespaces where tablespace_name='SYSTEM';
TABLESPACE_NAME BLOCK_SIZE EXTENT_MAN SEGMEN
------------------------------ ---------- ---------- ------
SYSTEM 8192 LOCAL MANUAL
SYS@PROD>select obj# from obj$ where name='T4';
OBJ#
----------
13587
SYS@PROD>alter session set events 'immediate trace name buffers level 1';
Session altered.
--ONLY ONE BH FOR SEGMENT HEADER
BH (0x70beb4d8) file#: 1 rdba: 0x00407e90 (1/32400) class: 4 ba: 0x70a0a000
set: 12 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 186,28
dbwrid: 0 obj: 13587 objn: 13587 tsn: 0 afn: 1 hint: f
hash: lru:
obj-flags: object_ckpt_list
ckptq: fileq: objq:
st: XCURRENT md: NULL tch: 1
flags: buffer_dirty redo_since_read
LRBA: LSCN: HSCN: HSUB:
cr pin refcnt: 0 sh pin refcnt: 0
此时只有一个segment header block header。
2、向sys.t4表中插入一条记录
SYS@PROD>insert into t4 values(1,2);
1 row created.
SYS@PROD>commit;
Commit complete.
SYS@PROD>alter session set events 'immediate trace name buffers level 1';
Session altered.
SYS@PROD>select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from t4;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
1 32401
--2 BH: 1 FOR SEGMENT HEADER 1 FOR DATA BLOCK. what des pwc means?
BH (0x70beb4d8) file#: 1 rdba: 0x00407e90 (1/32400) class: 4 ba: 0x70a0a000
set: 12 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 186,28
dbwrid: 0 obj: 13587 objn: 13587 tsn: 0 afn: 1 hint: f
hash: lru:
obj-flags: object_ckpt_list
ckptq: fileq: objq:
st: XCURRENT md: NULL tch: 1
flags: buffer_dirty redo_since_read
LRBA: LSCN: HSCN: HSUB:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x70beb4d8) file#: 1 rdba: 0x00407e90 (1/32400) class: 4 ba: 0x70a0a000
set: 12 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 199,28
dbwrid: 0 obj: 13587 objn: 13587 tsn: 0 afn: 1 hint: f
hash: lru:
obj-flags: object_ckpt_list
ckptq: fileq: objq:
st: XCURRENT md: NULL tch: 2
flags: buffer_dirty redo_since_read
LRBA: LSCN: HSCN: HSUB:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x707d8148) file#: 1 rdba: 0x00407e91 (1/32401) class: 1 ba: 0x70404000
set: 9 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 184,28
dbwrid: 0 obj: 13587 objn: 13587 tsn: 0 afn: 1 hint: f
hash: lru:
obj-flags: object_ckpt_list
ckptq: fileq: objq:
st: XCURRENT md: NULL tch: 1
flags: buffer_dirty redo_since_read
LRBA: LSCN: HSCN: HSUB:
cr pin refcnt: 0 sh pin refcnt: 0
可以发现多了一个CLASS=1的DATA BLOCK HEADER，且仅有一个。
3、删表
SYS@PROD>drop table t4 purge;
Table dropped.
SYS@PROD>alter session set events 'immediate trace name buffers level 1';
Session altered.
-- see what happed below:
BH (0x70beb4d8) file#: 1 rdba: 0x00407e90 (1/32400) class: 4 ba: 0x70a0a000
set: 12 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 186,28
dbwrid: 0 obj: 13587 objn: 13587 tsn: 0 afn: 1 hint: f
hash: lru:
obj-flags: object_ckpt_list
ckptq: fileq: objq:
st: XCURRENT md: NULL tch: 1
flags: buffer_dirty redo_since_read
LRBA: LSCN: HSCN: HSUB:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x70beb4d8) file#: 1 rdba: 0x00407e90 (1/32400) class: 4 ba: 0x70a0a000
set: 12 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 199,28
dbwrid: 0 obj: 13587 objn: 13587 tsn: 0 afn: 1 hint: f
hash: lru:
obj-flags: object_ckpt_list
ckptq: fileq: objq:
st: XCURRENT md: NULL tch: 2
flags: buffer_dirty redo_since_read
LRBA: LSCN: HSCN: HSUB:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x707d8148) file#: 1 rdba: 0x00407e91 (1/32401) class: 1 ba: 0x70404000
set: 9 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 184,28
dbwrid: 0 obj: 13587 objn: 13587 tsn: 0 afn: 1 hint: f
hash: lru:
obj-flags: object_ckpt_list
ckptq: fileq: objq:
st: XCURRENT md: NULL tch: 1
flags: buffer_dirty redo_since_read
LRBA: LSCN: HSCN: HSUB:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x707e1358) file#: 1 rdba: 0x00407e90 (1/32400) class: 4 ba: 0x704fa000
set: 10 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 200,19
dbwrid: 0 obj: 13587 objn: 0 tsn: 0 afn: 1 hint: f
hash: lru:
lru-flags: on_auxiliary_list
ckptq: fileq: objq:
st: FREE md: NULL tch: 0 lfb: 33
flags:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x70beb4d8) file#: 1 rdba: 0x00407e90 (1/32400) class: 4 ba: 0x70a0a000
set: 12 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 205,19
dbwrid: 0 obj: 13587 objn: 13587 tsn: 0 afn: 1 hint: f
hash: lru:
lru-flags: on_auxiliary_list
ckptq: fileq: objq:
st: FREE md: NULL tch: 0 lfb: 33
flags:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x707d8148) file#: 1 rdba: 0x00407e91 (1/32401) class: 1 ba: 0x70404000
set: 9 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 190,19
dbwrid: 0 obj: 13587 objn: 13587 tsn: 0 afn: 1 hint: f
hash: lru:
lru-flags: on_auxiliary_list
ckptq: fileq: objq:
st: FREE md: NULL tch: 0 lfb: 33
flags:
cr pin refcnt: 0 sh pin refcnt: 0 接上：
二、在SEGMENT SPACE MANAGEMENT=AUTO的EXAMPLE空间下建表：
1、建表zn.t8
SYS@PROD>create table zn.t8(x int, y int);
Table created.
SYS@PROD>select obj# from obj$ where name='T8';
OBJ#
----------
13591
SYS@PROD>alter session set events 'immediate trace name buffers level 1';
Session altered.
SYS@PROD>select * from v$diag_info where name='Default Trace File';
INST_ID NAME VALUE
---------- ---------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 Default Trace File /s01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_7243.trc
vi /s01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_7243.trc
找不到objn: 13591的BH
同样发现此时在DBA_SEGMENTS和ZN.USER_SEGMENTS里都找不到SEGMENT_NAME='T8'的记录
2、插入一条记录zn.t8
SYS@PROD>insert into zn.t8 values(1,2);
1 row created.
SYS@PROD>select segment_name,tablespace_name from dba_segments where segment_name='T8';
SEGMENT_NA TABLESPACE_NAME
---------- ------------------------------
T8 EXAMPLE
SYS@PROD>roll back;
Rollback complete.
SYS@PROD>select segment_name,tablespace_name from dba_segments where segment_name='T8';
SEGMENT_NA TABLESPACE_NAME
---------- ------------------------------
T8 EXAMPLE
此时再做dump查看并查看trace file
SYS@PROD>alter session set events 'immediate trace name buffers level 1';
Session altered.
发现如下出现8个BH，对应的OBJECT_ID=13591-----ZN.T8!重现了我的帖子问题。但是此时我就可以理解一些了。
自动段空间管理预分配了5个DATA BLOCK，再加上1个SEGMENT HEADER和CLASS=8/9的两个块（用处不明），合计8个。
即使我上面的插入没有提交，做了回滚，依然分配了5个DATA BLOCK。
最后重新做插入和提交，直接将ROW放在了229号块上。
BH (0x6fbe8428) file#: 4 rdba: 0x010000e1 (4/225) class: 9 ba: 0x6f9b8000
set: 11 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 205,28
dbwrid: 0 obj: 13591 objn: 13591 tsn: 4 afn: 4 hint: f
hash: lru:
obj-flags: object_ckpt_list
ckptq: fileq: objq:
st: XCURRENT md: NULL tch: 1
flags: buffer_dirty redo_since_read
LRBA: LSCN: HSCN: HSUB:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x753e7128) file#: 4 rdba: 0x010000e6 (4/230) class: 1 ba: 0x75198000
set: 9 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 196,28
dbwrid: 0 obj: 13591 objn: 13591 tsn: 4 afn: 4 hint: f
hash: lru:
obj-flags: object_ckpt_list
ckptq: fileq: objq:
st: XCURRENT md: NULL tch: 1
flags: buffer_dirty redo_since_read
LRBA: LSCN: HSCN: HSUB:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x6fbdeaf8) file#: 4 rdba: 0x010000e3 (4/227) class: 1 ba: 0x6f8b6000
set: 10 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 206,19
dbwrid: 0 obj: 13591 objn: 13591 tsn: 4 afn: 4 hint: f
hash: lru:
obj-flags: object_ckpt_list
ckptq: fileq: objq:
st: XCURRENT md: NULL tch: 1
flags: buffer_dirty redo_since_read
LRBA: LSCN: HSCN: HSUB:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x6fbf2df8) file#: 4 rdba: 0x010000e0 (4/224) class: 8 ba: 0x6fad6000
set: 12 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 215,19
dbwrid: 0 obj: 13591 objn: 13591 tsn: 4 afn: 4 hint: f
hash: lru:
obj-flags: object_ckpt_list
ckptq: fileq: objq:
st: XCURRENT md: NULL tch: 2
flags: buffer_dirty redo_since_read
LRBA: LSCN: HSCN: HSUB:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x6fbf2b98) file#: 4 rdba: 0x010000e5 (4/229) class: 1 ba: 0x6fad2000
set: 12 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 215,19
dbwrid: 0 obj: 13591 objn: 13591 tsn: 4 afn: 4 hint: f
hash: lru:
obj-flags: object_ckpt_list
ckptq: fileq: objq:
st: XCURRENT md: NULL tch: 2
flags: buffer_dirty redo_since_read
LRBA: LSCN: HSCN: HSUB:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x6fbdec28) file#: 4 rdba: 0x010000e2 (4/226) class: 4 ba: 0x6f8b8000
set: 10 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 206,19
dbwrid: 0 obj: 13591 objn: 13591 tsn: 4 afn: 4 hint: f
hash: lru:
obj-flags: object_ckpt_list
ckptq: fileq: objq:
st: XCURRENT md: NULL tch: 1
flags: buffer_dirty redo_since_read
LRBA: LSCN: HSCN: HSUB:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x6fbde9c8) file#: 4 rdba: 0x010000e7 (4/231) class: 1 ba: 0x6f8b4000
set: 10 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 206,19
dbwrid: 0 obj: 13591 objn: 13591 tsn: 4 afn: 4 hint: f
hash: lru:
obj-flags: object_ckpt_list
ckptq: fileq: objq:
st: XCURRENT md: NULL tch: 1
flags: buffer_dirty redo_since_read
LRBA: LSCN: HSCN: HSUB:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x6fbe81c8) file#: 4 rdba: 0x010000e4 (4/228) class: 1 ba: 0x6f9b4000
set: 11 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 205,28
dbwrid: 0 obj: 13591 objn: 13591 tsn: 4 afn: 4 hint: f
hash: lru:
obj-flags: object_ckpt_list
ckptq: fileq: objq:
st: XCURRENT md: NULL tch: 1
flags: buffer_dirty redo_since_read
LRBA: LSCN: HSCN: HSUB:
cr pin refcnt: 0 sh pin refcnt: 0
SYS@PROD>select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from zn.t8;
no rows selected
SYS@PROD>insert into zn.t8 values(1,2);
1 row created.
SYS@PROD>commit;
Commit complete.
SYS@PROD>select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from zn.t8;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
4 229
不知上面我的理解和实验过程是否正确，请各位大侠指正。 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
INSTANCE_NAME
----------------
Parnassus
SQL> alter system flush buffer_cache;
System altered.
SQL> create table maclean (t1 int) tablespace users;
Table created.
SQL> insert into maclean values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# from maclean;
FILE# BLOCK#
---------- ----------
4 136159
SQL> select obj#,dataobj# from obj$ where name='MACLEAN';
OBJ# DATAOBJ#
---------- ----------
79311 79311
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump buffers 1;
oradebug tracefile_name
BH (0x85ff1c18) file#: 4 rdba: 0x010213dc (4/136156) class: 1 ba: 0x85e82000
set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,28
dbwrid: 0 obj: 79311 objn: 79311 tsn: 4 afn: 4 hint: f
hash: lru:
obj-flags: object_ckpt_list
ckptq: fileq: objq: objaq:
st: XCURRENT md: NULL fpin: 'ktspbwh2: ktspfmdb' tch: 2
flags: buffer_dirty redo_since_read
LRBA: LSCN: HSCN: HSUB:
BH (0x85ff2cb8) file#: 4 rdba: 0x010213d9 (4/136153) class: 9 ba: 0x85e9e000
set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,28
dbwrid: 0 obj: 79311 objn: 79311 tsn: 4 afn: 4 hint: f
hash: lru:
obj-flags: object_ckpt_list
ckptq: fileq: objq: objaq:
st: XCURRENT md: NULL fpin: 'ktspswh4: ktspfsbmb' tch: 2
flags: buffer_dirty redo_since_read
LRBA: LSCN: HSCN: HSUB:
BH (0x85ff1ae8) file#: 4 rdba: 0x010213de (4/136158) class: 1 ba: 0x85e80000
set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,28
dbwrid: 0 obj: 79311 objn: 79311 tsn: 4 afn: 4 hint: f
hash: lru:
obj-flags: object_ckpt_list
ckptq: fileq: objq: objaq:
st: XCURRENT md: NULL fpin: 'ktspbwh2: ktspfmdb' tch: 2
flags: buffer_dirty redo_since_read
LRBA: LSCN: HSCN: HSUB:
BH (0x90fe9bd8) file#: 4 rdba: 0x010213db (4/136155) class: 1 ba: 0x90daa000
set: 6 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,15
dbwrid: 0 obj: 79311 objn: 79311 tsn: 4 afn: 4 hint: f
hash: lru:
obj-flags: object_ckpt_list
ckptq: fileq: objq: objaq:
st: XCURRENT md: NULL fpin: 'ktspbwh2: ktspfmdb' tch: 2
flags: buffer_dirty redo_since_read
LRBA: LSCN: HSCN: HSUB:
BH (0x90feaa18) file#: 4 rdba: 0x010213d8 (4/136152) class: 8 ba: 0x90dc2000
set: 6 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,15
dbwrid: 0 obj: 79311 objn: 79311 tsn: 4 afn: 4 hint: f
hash: lru:
obj-flags: object_ckpt_list
ckptq: fileq: objq: objaq:
st: XCURRENT md: NULL fpin: 'ktspfwh6: ktspffbmb' tch: 2
flags: buffer_dirty redo_since_read
LRBA: LSCN: HSCN: HSUB:
BH (0x90fe9aa8) file#: 4 rdba: 0x010213dd (4/136157) class: 1 ba: 0x90da8000
set: 6 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,15
dbwrid: 0 obj: 79311 objn: 79311 tsn: 4 afn: 4 hint: f
hash: lru:
obj-flags: object_ckpt_list
ckptq: fileq: objq: objaq:
st: XCURRENT md: NULL fpin: 'ktspbwh2: ktspfmdb' tch: 2
flags: buffer_dirty redo_since_read
LRBA: LSCN: HSCN: HSUB:
BH (0x90fefd38) file#: 4 rdba: 0x010213da (4/136154) class: 4 ba: 0x90e4e000
set: 6 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,15
dbwrid: 0 obj: 79311 objn: 79311 tsn: 4 afn: 4 hint: f
hash: lru:
obj-flags: object_ckpt_list
ckptq: fileq: objq: objaq:
st: XCURRENT md: NULL fpin: 'ktswh03: ktscts' tch: 3
flags: buffer_dirty redo_since_read
LRBA: LSCN: HSCN: HSUB:
BH (0x90fe8ff8) file#: 4 rdba: 0x010213df (4/136159) class: 1 ba: 0x90d96000
set: 6 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,15
dbwrid: 0 obj: 79311 objn: 79311 tsn: 4 afn: 4 hint: f
hash: lru:
obj-flags: object_ckpt_list
ckptq: fileq: objq: objaq:
st: XCURRENT md: NULL fpin: 'ktspbwh2: ktspfmdb' tch: 2
flags: buffer_dirty redo_since_read
LRBA: LSCN: HSCN: HSUB:
CLASS NUMBER See Note 33434.1
1,'data block',
2,'sort block',
3,'save undo block',
4,'segment header',
5,'save undo header',
6,'free list',
7,'extent map',
8,'1st level bmb',
9,'2nd level bmb',
10,'3rd level bmb',
11,'bitmap block',
12,'bitmap index block',
13,'file header block',
14,'unused',
15,'system undo header',
16,'system undo block',
17,'undo header',
18,'undo block' -- since 10g
class 8 1st level bmb : 136152
class 9 2nd level bmb: 136153
class 4 segment header: 136154
class 1 data block: 136155 136156 136157 136158 136159
这是你看到的8个块
Low HighWater Mark :
Highwater:: 0x010213e0 ext#: 0 blk#: 8 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 5
mapblk 0x00000000 offset: 0
Level 1 BMB for High HWM block: 0x010213d8
Level 1 BMB for Low HWM block: 0x010213d8
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x010213d9
Last Level 1 BMB: 0x010213d8
Last Level II BMB: 0x010213d9
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 1 obj#: 79311 flag: 0x10000000
Inc # 0
Extent Map
Highwater:: 0x010213e0 ==》 4/ 136160
L2 Hint for inserts: 0x010213d9 ==> 4/136153 ==>2nd level bmb
感谢Maclean的指点，虽然Low HighWater Mark下面一段因还没学到过看不懂，但感觉有了方向了。另外，请教一点，像BUFFER CACHE、BH、BLOCK这几块知识，有必要把dump里的内容每个关键词、每次dump出来的变化都搞懂搞透吗？对于除了学院派以外的帮助会有哪些？
如果有必要，从方法上来说，对于我这种刚起步的，是不是没必要立即弄得很清楚，随着学习的进步，再不断地去深入学习会更好？ 对于诊断一些与 数据块有关的内部错误 还有数据块的损坏、数据段的损坏是有价值的。
随着你对ORA的诊断经历变多，会逐渐熟悉 Liu Maclean(刘相兵 发表于 2014-4-9 09:15 static/image/common/back.gif
对于诊断一些与 数据块有关的内部错误 还有数据块的损坏、数据段的损坏是有价值的。
随着你对ORA的诊断经 ...
多谢Maclean指点！ 继续去学习文档并做实验，会逐渐搞清楚的。问题关闭，多谢！
页:
[1]