datablock （二）好像难产了一样。终于出来了，反正，继续学习吧
环境是11204，linux6.5
SQL> select * from v$version where rownum<2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
1、来看看测试表sa.t1
SQL> select * from sa.t1;
ID NAME
---- ----------
1 ww
2 wws
3 wsws
4 test2
2、看看它所在的数据文件和数据块
09:17:58 SQL> select TABLESPACE_NAME,block_id,file_id,blocks from dba_extents where owner='SA' and segment_name='T1';
TABLESPACE_NAME BLOCK_ID FILE_ID BLOCKS
------------------------------ ---------- ---------- ----------
TEST 136 6 8
3、可以看到起始块是6号数据文件的第136个块，这个extent有8个块。136~138这三个数据块是不存放数据的（first/second LEVEL BITMAP BLOCK），真正存放数据的是从139开始的块，可以alter system dump datafile 6 block 136看看trace文件.（sid_ora_xxxx.trc）
*** 2016-08-31 08:40:17.111
Block dump from cache:
Dump of buffer cache at level 4 for tsn=6 rdba=25165960
Block dump from disk:
buffer tsn: 6 rdba: 0x01800088 (6/136)
scn: 0x0000.0011bf54 seq: 0x03 flg: 0x04 tail: 0xbf542003
frmt: 0x02 chkval: 0xefc6 type: 0x20=FIRST LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F7A88888200 to 0x00007F7A8888A200
7F7A88888200 0000A220 01800088 0011BF54 04030000 [ .......T.......]
7F7A88888210 0000EFC6 00000000 00000000 00000000 [................]
7F7A88888220 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
7F7A88888240 00000000 00000000 00000000 00000004 [................]
7F7A88888250 FFFFFFFF 00000004 00000003 00000008 [................]
7F7A88888260 00010001 00000000 00000000 00000000 [................]
7F7A88888270 00000000 00000004 00000000 00000000 [................]
7F7A88888280 00000000 0013000A 0000036F 00000002 [........o.......]
7F7A88888290 01800089 00000000 00000000 00000004 [................]
7F7A888882A0 00000008 0180008C 00000000 00000000 [................]
7F7A888882B0 00000000 00000004 00000000 00000001 [................]
7F7A888882C0 00007B9A 00000003 00000000 01800088 [.{..............]
7F7A888882D0 00000008 00000000 00000000 00000000 [................]
7F7A888882E0 00000000 00000000 00000000 00000000 [................]
Repeat 9 times
7F7A88888380 00000000 00000000 00000000 00001111 [................]
7F7A88888390 00000000 00000000 00000000 00000000 [................]
Repeat 485 times
7F7A8888A1F0 00000000 00000000 00000000 BF542003 [............. T.]
Dump of First Level Bitmap Block
--------------------------------
nbits : 4 nranges: 1 parent dba: 0x01800089 poffset: 0
unformatted: 4 total: 8 first useful block: 3
owning instance : 1
instance ownership changed at
Last successful Search
Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0
Extent Map Block Offset: 4294967295
First free datablock : 4
Bitmap block lock opcode 2
Locker xid: : 0x000a.013.0000036f
Dealloc scn: 3.0
Flag: 0x00000001 (-/-/-/-/-/HWM)
Inc #: 0 Objd: 31642
HWM Flag: HWM Set
Highwater:: 0x0180008c ext#: 0 blk#: 4 ext size: 8 --------------------->鼎鼎有名的hwm
#blocks in seg. hdr's freelists: 0
#blocks below: 4
mapblk 0x00000000 offset: 0
--------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
0x01800088 Length: 8 Offset: 0
0:Metadata 1:Metadata 2:Metadata 3:FULL
4:unformatted 5:unformatted 6:unformatted 7:unformatted ----------------->块的使用情况 --------------------------------------------------------
End dump data blocks tsn: 6 file#: 6 minblk 136 maxblk 136
4、bbed也可以看一下这三个块的不同
BBED> set filename '/oradata/li/1'
FILENAME /oradata/li/1
BBED> set block 136
BLOCK# 136
BBED> map
File: /oradata/li/1 (0)
Block: 136 Dba:0x00000000
------------------------------------------------------------
BBED-00400: invalid blocktype (32)
BBED> dump
File: /oradata/li/1 (0)
Block: 136 Offsets: 0 to 511 Dba:0x00000000
------------------------------------------------------------------------
20a20000 88008001 54bf1100 00000304 c6ef0000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 04000000 ffffffff 04000000 03000000 08000000
01000100 00000000 00000000 00000000 00000000 04000000 00000000 00000000
00000000 0a001300 6f030000 02000000 89008001 00000000 00000000 04000000
08000000 8c008001 00000000 00000000 00000000 04000000 00000000 01000000
9a7b0000 03000000 00000000 88008001 08000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 11110000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes="" per="" line="">
BBED>
BBED> set block 139
BLOCK# 139
BBED> map
File: /oradata/li/1 (0)
Block: 139 Dba:0x00000000
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 96 bytes @20
struct kdbh, 14 bytes @124
struct kdbt[1], 4 bytes @138
sb2 kdbr[4] @142
ub1 freespace[7996] @150
ub1 rowdata[42] @8146
ub4 tailchk @8188
BBED>
BBED> dump
File: /oradata/li/1 (0)
Block: 139 Offsets: 0 to 511 Dba:0x00000000
------------------------------------------------------------------------
06a20000 8b008001 cbcd1100 00000104 24ab0000 01000000 9a7b0000 cbcd1100
00000000 03003200 88008001 0a001900 6c030000 00000000 00000000 00a00000
d1ca1100 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00010400
ffff1a00 561f3c1f 3c1f0000 0400771f 6d1f621f 561f0000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes="" per="" line="">
5、再dump一下139这个块看看（alter system dump datafile 6 block 139 同样是sid_ora_xxx.trc文件）
Start dump data blocks tsn: 6 file#:6 minblk 139 maxblk 139
*** 2016-08-31 08:55:26.860
Block dump from cache:
Dump of buffer cache at level 4 for tsn=6 rdba=25165963
BH (0x6a3efad0) file#: 6 rdba: 0x0180008b (6/139) class: 1 ba: 0x6a2a4000
set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 422,28
dbwrid: 0 obj: 31642 objn: 31611 tsn: 6 afn: 6 hint: f
hash: [0x6e99a9c0,0x6e99a9c0] lru: [0x6a3efcf8,0x6a3efa88]
ckptq: [NULL] fileq: [NULL] objq: [0x6a3efd20,0x671c4ce0] objaq: [0x6a3efd30,0x671c4cd0]
st: XCURRENT md: NULL fpin: 'kdswh01: kdstgr' tch: 2
flags: only_sequential_access
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
Block dump from disk:
buffer tsn: 6 rdba: 0x0180008b (6/139)
scn: 0x0000.0011cdcb seq: 0x01 flg: 0x04 tail: 0xcdcb0601
frmt: 0x02 chkval: 0xab24 type: 0x06=trans data -------------------------->数据块
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F7A88888200 to 0x00007F7A8888A200
7F7A88888200 0000A206 0180008B 0011CDCB 04010000 [................]
7F7A88888210 0000AB24 00000001 00007B9A 0011CDCB [$........{......]
7F7A88888220 00000000 00320003 01800088 0019000A [......2.........]
7F7A88888230 0000036C 00000000 00000000 0000A000 [l...............]
7F7A88888240 0011CAD1 00000000 00000000 00000000 [................]
7F7A88888250 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
7F7A88888270 00000000 00000000 00000000 00040100 [................]
7F7A88888280 001AFFFF 1F3C1F56 00001F3C 1F770004 [....V.<.<.....w.]
7F7A88888290 1F621F6D 00001F56 00000000 00000000 [m.b.V...........]
7F7A888882A0 00000000 00000000 00000000 00000000 [................]
Repeat 498 times
7F7A8888A1D0 002C0000 05C10202 73657405 002C3274 [..,......test2,.]
7F7A8888A1E0 04C10202 77737704 02002C73 0303C102 [.....wsws,......]
7F7A8888A1F0 2C737777 C1020200 77770202 CDCB0601 [wws,......ww....]
Block header dump: 0x0180008b
Object id on Block? Y
seg/obj: 0x7b9a csc: 0x00.11cdcb itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1800088 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.019.0000036c 0x00000000.0000.00 C-U- 0 scn 0x0000.0011cad1
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x0180008b
data_block_dump,data header at 0x7f7a8888827c
===============
tsiz: 0x1f80
hsiz: 0x1a
pbl: 0x7f7a8888827c
76543210
flag=--------
ntab=1
nrow=4
frre=-1
fsbo=0x1a
fseo=0x1f56
avsp=0x1f3c
tosp=0x1f3c
0xe:pti[0] nrow=4 offs=0
0x12:pri[0] offs=0x1f77
0x14:pri[1] offs=0x1f6d
0x16:pri[2] offs=0x1f62
0x18:pri[3] offs=0x1f56
block_row_dump:
tab 0, row 0, @0x1f77
tl: 9 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 02
col 1: [ 2] 77 77
tab 0, row 1, @0x1f6d
tl: 10 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 03
col 1: [ 3] 77 77 73
tab 0, row 2, @0x1f62
tl: 11 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 04
col 1: [ 4] 77 73 77 73
tab 0, row 3, @0x1f56
tl: 12 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 05
col 1: [ 5] 74 65 73 74 32
end_of_block_dump
End dump data blocks tsn: 6 file#: 6 minblk 139 maxblk 139
6、内容很多，多看几遍找自己需要的东西，比如说dump文件末尾的col 1: [ 2] 77 77这行，这是第一列的值，当然是16进制的。77转换成10进制就是119
[oracle@test scripts]$ ./num.sh （脚本内容在最后）
input under one of :
1 16------>2
2 16------>10
2
imput the number you want to change
77
and so it's 119
[oracle@test scripts]$
我们比对下ASCII表的119看看是什么，是w
再回过头看看sa.t1
SQL> select * from sa.t1;
ID NAME
---- ----------
1 ww
2 wws
3 wsws
4 test2
有点意思。
附：num.sh脚本内容，16进制转换10进制和2进制
#! /bin/bash
echo "input under one of :
1 16------>2
2 16------>10
"
read num
case $num in
1) echo "imput the number you want to change"
read n
echo -e "and so it's \c"
echo "ibase=16;obase=2;$n"|bc;;
2) echo "imput the number you want to change"
read n
echo -e "and so it's \c"
echo "obase= 10 ;ibase=16;$n" | bc;;
*) echo "not correct input";;
esac
2016-08-31
亮仔
八月的最后一天，明天开始去值班了。加油！
阅读(2281) | 评论(0) | 转发(0) |
