初识oracle block系列(一)

最新推荐文章于 2024-03-15 08:00:00 发布

clg10051

最新推荐文章于 2024-03-15 08:00:00 发布

阅读量167

点赞数

文章标签：数据库数据结构与算法

--测试场景
SQL> select * from v$version where rownum=1;

BANNER
------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SQL>

----使用命令

Oracle 7之前
ALTER SESSION SET EVENTS 'immediate trace name blockdump level n';
n为block的rdba
Oracle8以后
ALTER SYSTEM DUMP DATAFILE file# BLOCK block#;
ALTER SYSTEM DUMP DATAFILE file#
BLOCK MIN minimum_block#
BLOCK MAX maximum_block#;

---创建测试并插入数据
SQL> create table t_block(a int,b int);

表已创建。

SQL> insert into t_block select level,level from dual connect by level<=3e2;

已创建300行。

SQL> commit;

提交完成。

-----获取测试表某条记录所属的块号及文件号
SQL> select dbms_rowid.rowid_to_absolute_fno(rowid,'SCOTT','T_BLOCK'),dbms_rowid
.rowid_block_number(rowid) from t_block where rownum=1;

DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO(ROWID,'SCOTT','T_BLOCK')
---------------------------------------------------------
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
5
134

SQL> select file_id from dba_data_files where tablespace_name='SELF_LEARN';

FILE_ID
----------
5

----自dba_segment可得知此段表的头文件是5,段头块是130
SQL> select segment_name,header_file,header_block from dba_segments where segme
nt_name='T_BLOCK';

SEGMENT_NAME
--------------------------------------------------------------------------------

HEADER_FILE HEADER_BLOCK
----------- ------------
T_BLOCK
          5          130


--segment header block存储内容到底是什么呢?

互联网关于dump block的文章列表:
http://blog.csdn.net/tianlesoftware/article/details/6529346
-----下面开始简约描述dump block大致内容
---鉴于篇幅过大,略去不重要的内容------------------------------------

----推知自表空间5,数据块130转储文件,tsn为tablespace number file#为文件编号
Start dump data blocks tsn: 5 file#:5 minblk 130 maxblk 130
--自缓存转储数据块
Block dump from cache:

--转化数据块地址dba为块号block number
SQL> select dbms_utility.data_block_address_block(20971650) from dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(20971650)
-----------------------------------------------
130

SQL>
Dump of buffer cache at level 4 for tsn=5, rdba=20971650 ---rdba即表t_block segment header block的数据块地址

SQL> select file#,block#,ts# from v$bh where block#=130;

FILE# BLOCK# TS#
---------- ---------- ----------
5 130 5
--说明segment header已在缓存buffer cache中
SQL> select ts#,tch,state,obj from x$bh where ba='000007FF4C668000';

TS# TCH STATE OBJ
---------- ---------- ---------- ----------
5 3 1 66434
SQL> select object_name from dba_objects where object_id=66434;

OBJECT_NAME
----------------------------------------------------------------

T_BLOCK

-- ba即数据块地址
BH (0x000007FF4CFA2CA8) file#: 5 rdba: 0x01400082 (5/130) class: 4 ba: 0x000007FF4C668000
set: 9 pool 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,28
dbwrid: 0 obj: 66434 objn: 66434 tsn: 5 afn: 5 hint: f

SQL> select hladdr,nxt_hash,prv_hash,lru_flag,obj_flag,dirty_queue from x$bh wh
ere ba='000007FF4C668000';

HLADDR           NXT_HASH         PRV_HASH           LRU_FLAG   OBJ_FLAG
---------------- ---------------- ---------------- ---------- ----------
DIRTY_QUEUE
-----------
000007FF603C8220 000007FF603CA3A8 000007FF603CA3A8          0        240
          0
---存储双向hash链表的上和后一个hash的地址
hash: [0x000007FF603CA3A8,0x000007FF603CA3A8] lru: [0x000007FF4CFA2EC0,0x000007FF4CFA2C60] --增表记录后,lru的第二部分由0x000007FF4CFA2C60变为0x000007FF4CF9C2D8

---阅之前的文档可知是检查点事务队列,文件队列,对象队列
--此块内容要用其它的dump内容来查看了,在此不予讨论

---初次如下内容皆为空,
----增加表记录后如下变为:ckptq: [0x000007FF4DF87A08,0x000007FF51FD1798] fileq: [0x000007FF4DF87A18,0x000007FF4CFA2A58] objq: [0x000007FF4CFA2B58,0x000007FF4CF9C2D8]
----chktq,有值了,fileq同理,objq变更了,大家注意看[0x000007FF5CC4B890,0x000007FF4CFA2B58]变为[0x000007FF4CFA2B58,0x000007FF4CF9C2D8],说明队列是采用双向链表设计
ckptq: [NULL] fileq: [NULL] objq: [0x000007FF5CC4B890,0x000007FF4CFA2B58]

--st是status的缩写,XCURRENT表当前模式
--tch映射x$bh的tch,命名此块的个块
----增加表记录后,如下变为:st: XCURRENT md: NULL tch: 8,命中表的次数变为8
st: XCURRENT md: NULL tch: 3

---由原来的block_written_once redo_since_read变为buffer_dirty block_written_once redo_since_read,说明有了修改块的操作
flags: block_written_once redo_since_read --变更:flags: buffer_dirty block_written_once redo_since_read(自下不再标明:增加表记录后述语,直接写变更)

SQL> select lrba_seq,lrba_bno,hscn_bas from x$bh where ba='000007FF4C668000';

LRBA_SEQ LRBA_BNO HSCN_BAS
---------- ---------- ----------
0 0 4294967295

SQL> select to_char(4294967295,'xxxxxxxxxxxxxxx') from dual;

TO_CHAR(4294967295,'XXXXXXXXXXXX
--------------------------------
ffffffff

SQL> select hsub_scn from x$bh where ba='000007FF4C668000';

HSUB_SCN
----------
1

--------lrba的地址及lscn由上sql可知.皆从0开始计数,hscn表示最高的scn,hsub不太理解是什么
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1] --变更:--变更:LRBA: [0x2f.5587.0] LSCN: [0x0.b053c] HSCN: [0x0.b074d] HSUB: [2],发现没,全变了,不再是0

--如下cr即一致性读,sh表什么,refcnt?
cr pin refcnt: 0 sh pin refcnt: 0 --变更:cr pin refcnt: 0 sh pin refcnt: 0,未发生变化
Block dump from disk:
buffer tsn: 5 rdba: 0x01400082 (5/130)

--scn是什么的scn,tail又是什么?
scn: 0x0000.000af9c9 seq: 0x01 flg: 0x04 tail: 0xf9c92301 --变更:scn: 0x0000.000af9c9 seq: 0x01 flg: 0x04 tail: 0xf9c92301未变化

---frmt??
--type表明是segment header
frmt: 0x02 chkval: 0xa802 type: 0x23=PAGETABLE SEGMENT HEADER --变更:frmt: 0x02 chkval: 0xa802 type: 0x23=PAGETABLE SEGMENT HEADER 未变化
Hex dump of block: st=0, typ_found=1

--如下表明自0x000000001C8F8200到0x000000001C8FA200进行转储

--这里面的16进制的东东到底是什么呢?
Dump of memory from 0x000000001C8F8200 to 0x000000001C8FA200 --变更:Dump of memory from 0x000000001D3E6C00 to 0x000000001D3E8C00 ,变化了
01C8F8200 0000A223 01400082 000AF9C9 04010000 [#.....@.........]
01C8F8210 0000A802 00000000 00000000 00000000 [................]
01C8F8220 00000000 00000001 00000008 00000A9C [................]
01C8F8230 00000000 00000008 00000008 01400088 [..............@.]
01C8F8240 00000000 00000000 00000000 00000005 [................]
01C8F8250 00000000 00000000 00000000 00000000 [................]
01C8F8260 00000008 00000008 01400088 00000000 [..........@.....]
01C8F8270 00000000 00000000 00000005 01400080 [..............@.]
01C8F8280 01400080 00000000 00000000 00000000 [..@.............]
01C8F8290 00000000 00000000 00000000 00000000 [................]
        Repeat 3 times --重复3次,什么重复??
01C8F82D0 00000001 00002000 00000000 00001434 [..... ......4...]
01C8F82E0 00000000 01400081 00000001 01400080 [......@.......@.]
01C8F82F0 01400081 00000000 00000000 00000000 [..@.............]
01C8F8300 00000000 00000000 00000001 00000000 [................]
01C8F8310 00010382 10000000 01400080 00000008 [..........@.....]
01C8F8320 00000000 00000000 00000000 00000000 [................]
        Repeat 152 times
01C8F8CB0 01400080 01400083 00000000 00000000 [..@...@.........]
01C8F8CC0 00000000 00000000 00000000 00000000 [................]
        Repeat 151 times
01C8F9640 00000000 00000000 01400081 00000000 [..........@.....]
01C8F9650 00000000 00000000 00000000 00000000 [................]
        Repeat 185 times
01C8FA1F0 00000000 00000000 00000000 F9C92301 [.............#..]

----区控制头块,综上,即segment header会存储它包含的extent的信息

---查段包含的区数
SQL> select segment_name,extents from dba_segments where segment_name='T_BLOCK';

SEGMENT_NAME
--------------------------------------------------------------------------------

EXTENTS
----------
T_BLOCK
1

---查段所属的区信息,综上,segment header block为130,而如下段启始块为128,说明3个块用于段头
--且,自131块开始共计5个块存储真实的表中记录
SQL> select segment_name,extent_id,block_id,blocks from dba_extents where segmen
t_name='T_BLOCK';

SEGMENT_NAME
--------------------------------------------------------------------------------

EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ----------
T_BLOCK
0 128 8

--区头块信息
Extent Control Header
-----------------------------------------------------------------
--如下说明仅一个区,区中的块数量为8
--此为段中区的总述
Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 8     -- 变更:Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 8
                  last map 0x00000000 #maps: 0      offset: 2716 --offset不知何意?
      --highwater是表的hwm,还是?

      --ext是区编号,在此仅一个区,故为0,blk#为块大小,ext size为区中个个数
      Highwater:: 0x01400088 ext#: 0      blk#: 8      ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 5
mapblk 0x00000000 offset: 0
                   Unlocked
--------------------------------------------------------
--低高水位标记
Low HighWater Mark :
      Highwater:: 0x01400088 ext#: 0      blk#: 8      ext size: 8
#blocks in seg. hdr's freelists: 0

#blocks below: 5
mapblk 0x00000000 offset: 0

--基于高(低)水位线块的1级bmb块地址,所谓1级bmb不理解,要查阅官方手册

--变更:对比之前发变化
/******************************
egment Type: 1 nl2: 1      blksz: 8192   fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x01400081
Last Level 1 BMB: 0x01400080
Last Level II BMB: 0x01400081
Last Level III BMB: 0x00000000
******************************/

Level 1 BMB for High HWM block: 0x01400080
Level 1 BMB for Low HWM block: 0x01400080
--------------------------------------------------------
Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x01400081
Last Level 1 BMB: 0x01400080
Last Level II BMB: 0x01400081
Last Level III BMB: 0x00000000

     --映射头,这个映射头
     Map Header:: next 0x00000000 #extents: 1    obj#: 66434 flag: 0x10000000 --变更:Map Header:: next 0x00000000 #extents: 1    obj#: 66434 flag: 0x10000000未变化
Inc # 0

---区映射
Extent Map
-----------------------------------------------------------------
   0x01400080 length: 8

--辅助映射
Auxillary Map
--------------------------------------------------------
   Extent 0     : L1 dba: 0x01400080 Data dba: 0x01400083 --Data dba也说明自第4个数据块开始存储表的记录,参上面的分析描述
--------------------------------------------------------

   Second Level Bitmap block DBAs
   --------------------------------------------------------
   DBA 1:   0x01400081

End dump data blocks tsn: 5 file#: 5 minblk 130 maxblk 130

小结:1,segment header由extent控制信息,extent map,Auxillary Map,二级位图块几个管理型信息单元构成
2,某些参数要学习dsi之后,方可更细致理解

数据块转储

alter system dump datafile 5 block 131;--131开始才是表记录的内容

---数据块转储
Start dump data blocks tsn: 5 file#:5 minblk 131 maxblk 131
Block dump from cache:
Dump of buffer cache at level 4 for tsn=5, rdba=20971651
BH (0x000007FF4CF80CC8) file#: 5 rdba: 0x01400083 (5/131) class: 1 ba: 0x000007FF4C2D4000
set: 12 pool 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,28
dbwrid: 0 obj: 66434 objn: 66434 tsn: 5 afn: 5 hint: f
hash: [0x000007FF60739F68,0x000007FF60739F68] lru: [0x000007FF4CF80EE0,0x000007FF4CF80C80]
ckptq: [NULL] fileq: [NULL] objq: [0x000007FF5CC51058,0x000007FF4CF80CA8]
st: XCURRENT md: NULL tch: 5
flags: block_written_once redo_since_read
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
buffer tsn: 5 rdba: 0x01400083 (5/131)
scn: 0x0000.000b0662 seq: 0x01 flg: 0x06 tail: 0x06620601
frmt: 0x02 chkval: 0x3ae7 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000001D3E6C00 to 0x000000001D3E8C00
01D3E6C00 0000A206 01400083 000B0662 06010000 [......@.b.......]
01D3E6C10 00003AE7 00000001 00010382 000AF9C8 [.:..............]
---相近数据从略

01D3E8BF0 012C03C1 08C20302 03C10242 06620601 [..,.....B.....b.]
Block header dump: 0x01400083
Object id on Block? Y
seg/obj: 0x10382 csc: 0x00.af9c8 itc: 2 flg: E typ: 1 - DATA
     brn: 0 bdba: 0x1400080 ver: 0x01 opc: 0
     inc: 0 exflg: 0

---存储事务信息,即哪些事务操作此表,一致性读或恢复自此获取信息
Itl           Xid                  Uba         Flag Lck        Scn/Fsc
0x01   0x0006.004.0000023e 0x00c0c936.0056.01 --U- 605 fsc 0x0000.000b0662
0x02   0x0000.000.00000000 0x00000000.0000.00 ----    0 fsc 0x0000.00000000
bdba: 0x01400083
data_block_dump,data header at 0x1d3e6c64
===============
tsiz: 0x1f98
hsiz: 0x4cc
pbl: 0x1d3e6c64
     76543210
flag=--------
ntab=1
nrow=605 --此块中表的记录数
frre=-1
fsbo=0x4cc
fseo=0x7fc
avsp=0x330
tosp=0x330
0xe:pti[0] nrow=605 offs=0

--如上是表中行在数据块的地址
SQL> select count(1) from t_block where dbms_rowid.rowid_block_number(rowid)=131
;

COUNT(1)
----------
605

--如下行条目共计605,与上述查询匹配
0x12:pri[0] offs=0x19b3
0x14:pri[1] offs=0x19bd
0x16:pri[2] offs=0x19c7
0x18:pri[3] offs=0x19d1
0x1a:pri[4] offs=0x19db
0x1c:pri[5] offs=0x19e5
0x1e:pri[6] offs=0x19ef
0x20:pri[7] offs=0x19f9
0x22:pri[8] offs=0x1a03
0x24:pri[9] offs=0x1a0d
0x26:pri[10] offs=0x1a17
0x28:pri[11] offs=0x1a21
0x2a:pri[12] offs=0x1a2b
0x2c:pri[13] offs=0x1a35
0x2e:pri[14] offs=0x1a3f
0x30:pri[15] offs=0x1a49
0x32:pri[16] offs=0x1a53
0x34:pri[17] offs=0x1a5d
0x36:pri[18] offs=0x1a67
0x38:pri[19] offs=0x1a71
0x3a:pri[20] offs=0x1a7b
0x3c:pri[21] offs=0x1a85
0x3e:pri[22] offs=0x1a8f
0x40:pri[23] offs=0x1a99
0x42:pri[24] offs=0x1aa3
0x44:pri[25] offs=0x1aad
0x46:pri[26] offs=0x1ab7
0x48:pri[27] offs=0x1ac1
0x4a:pri[28] offs=0x1acb
0x4c:pri[29] offs=0x1ad5
----中间相近数据从略--------------
0x4ca:pri[604] offs=0xfb6

--块中表的行的转储
block_row_dump:
tab 0, row 0, @0x19b3
tl: 10 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 3] c2 07 10 --表列1
col 1: [ 2] c1 03 -- ,表列2
tab 0, row 1, @0x19bd
--中间相近数据从略
tl: 10 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 3] c2 0d 14
col 1: [ 2] c1 03
end_of_block_dump
End dump data blocks tsn: 5 file#: 5 minblk 131 maxblk 131

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

转载于:http://blog.itpub.net/9240380/viewspace-751951/