bbed更改data block里的varchar型数据
此实验只是为了熟悉bbed用法和data block块的结构,在实际工作中这样的修改没有太大的实际意义
SQL> show userUSER is "SCOTT"
SQL> create table goolen (id number,name varchar2(50));
Table created.
SQL> insert into goolen values(1,'你是猪');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from goolen;
ID NAME
---------- --------------------------------------------------
1 你是猪
++++用bbed把“你是猪”改成“我是猪才怪”
+++我们先dump一下这两个值
SQL> select dump('你是猪','16') from dual;
DUMP
----------------------------------------
Typ=96 Len=9: e4,bd,a0,e6,98,af,e7,8c,aa ++++ e4bda0e698afe78caa
SQL> select dump('我是猪才怪','16') from dual;
DUMP
-----------------------------------------------------------
Typ=96 Len=15: e6,88,91,e6,98,af,e7,8c,aa,e6,89,8d,e6,80,aa +++e68891e698afe78caae6898de680aa
+++++一个长度是9,一个长度是15
++++下面我们看一下这条数据存储在哪个block里面
SQL> SELECT
2 dbms_rowid.rowid_relative_fno(rowid) REL_FNO,
3 dbms_rowid.rowid_block_number(rowid) BLOCKNO
4 from goolen;
REL_FNO BLOCKNO
---------- ----------
5 172 +++我们可以看到,这条数据存储在file 5 ,block 172(dba 5,172)
++++在用bbed修改数据之前,我们可以先dump一下这个block
SQL> conn / as sysdba
Connected.
SQL> oradebug setmypid
Statement processed.
SQL> alter system dump datafile 5 block 172;
System altered.
SQL> oradebug tracefile_name
/opt/app/oracle/diag/rdbms/goolen/goolen/trace/goolen_ora_925.trc
+++dump文件部分内容
block_row_dump:
tab 0, row 0, @0x1f88
tl: 16 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [ 9] e4 bd a0 e6 98 af e7 8c aa
end_of_block_dump
End dump data blocks tsn: 7 file#: 5 minblk 172 maxblk 172
++++我们可以看到id字段长度为2,name字段长度为9
++++下面我们开始用bbed来修改数据
[oracle@localhost bbed]$ bbed parfile=bbed.par
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Fri Nov 29 09:31:42 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> info
File# Name Size(blks)
----- ---- ----------
1 /opt/app/oracle/oradata/goolen/system01.dbf 89600
2 /opt/app/oracle/oradata/goolen/sysaux01.dbf 88320
3 /opt/app/oracle/oradata/goolen/undotbs01.dbf 16640
5 /opt/app/oracle/oradata/goolen/goolen01.dbf 1280
BBED> set file 5 block 172
FILE# 5
BLOCK# 172
BBED> find /x e4bda0
File: /opt/app/oracle/oradata/goolen/goolen01.dbf (5)
Block: 172 Offsets: 8179 to 8191 Dba:0x014000ac
------------------------------------------------------------------------
e4bda0e6 98afe78c aa0106a9 14
<32 bytes per line>
BBED> find /c 你
File: /opt/app/oracle/oradata/goolen/goolen01.dbf (5)
Block: 172 Offsets: 8179 to 8191 Dba:0x014000ac
------------------------------------------------------------------------
e4bda0e6 98afe78c aa0106a9 14
+++++我们可以看到,这条数据的name字段存储的位置
接下来我们完整的dump一下这一行数据
name字段存储在offsets 8179,我们再往前推进7个offset,进行dump
BBED> d /v offset 8172
File: /opt/app/oracle/oradata/goolen/goolen01.dbf (5)
Block: 172 Offsets: 8172 to 8191 Dba:0x014000ac
-------------------------------------------------------
2c010202 c10209e4 bda0e698 afe78caa l ,...n.你是猪
0106a914 l ..?.
+++我们来解释一下这些值的都代表什么意思:
2c表示row header信息,正常情况下就是2c
01表示改行数据的lock状态信息
02表示column数,这里为2个字段
02 第二个02表示后面跟着的一列数据的长度 ,这里是id = 1,长度为2,跟我们前面dump出来的结果吻合
c102 转换后为1,这里就是我们的id = 1的这个数
09表示后面跟着的一列数据的长度,我们这里为name字段,长度为9,跟上面的dump信息相同
e4bda0e698afe78caa
以上这些值代表了完整的一行数据
++++我们再来复习一下另外一个知识点
如果update一个varchar类型的数据,不管是长度变长了还是变短了,oralce都会为这一行数据重新分配存储空间,然后释放原来占有的空间
我们这个实验是要把name列的长度变长,如果我们用bbed直接去修改原来存储的值,那么肯定会出现乱码
我们这里只有一行数据,当然也可以把行头往前移动,依次往后修改这些值
如果这一行前面也存储着数据,就必须再往前找到free的空闲空间来存储数据
当然,你可以手工update一条数据,然后用bbed去观察,也会发现原有的值没有变,而是在新的存储空间存储数据
下面我们计算一下新数据一共要占用多少offsets
2c010202 c102 这些值不变
09为name字段的长度,改为“我是猪才怪”后,长度又9变为15,15转换后为0f
name值“我是猪才怪” 转换后为e68891e698afe78caae6898de680aa
那么修改完后完整的一行数据就是2c01 0202 c102 0fe6 8891 e698 afe7 8caa e689 8de6 80aa
一共占用22个offsets
原有的这一行数据是从offset 8172开始的,那么我们往前找22个offset来存储新值,即从offset 8150 开始
我们修改这些值:
m /x 2c01 offset 8150
m /x 0202 offset 8152
m /x c102 offset 8154
m /x 0fe6 offset 8156
m /x 8891 offset 8158
m /x e698 offset 8160
m /x afe7 offset 8162
m /x 8caa offset 8164
m /x e689 offset 8166
m /x 8de6 offset 8168
m /x 80aa offset 8170
BBED> m /x 2c01 offset 8150
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /opt/app/oracle/oradata/goolen/goolen01.dbf (5)
Block: 172 Offsets: 8150 to 8191 Dba:0x014000ac
------------------------------------------------------------------------
2c01053b 0d78710b 0a070837 18d4e730 1c3c02c1 0b012c01 0202c102 09e4bda0
e698afe7 8caa0106 a914
<32 bytes per line>
BBED> m /x 0202 offset 8152
File: /opt/app/oracle/oradata/goolen/goolen01.dbf (5)
Block: 172 Offsets: 8152 to 8191 Dba:0x014000ac
------------------------------------------------------------------------
02020d78 710b0a07 083718d4 e7301c3c 02c10b01 2c010202 c10209e4 bda0e698
afe78caa 0106a914
<32 bytes per line>
BBED> m /x c102 offset 8154
File: /opt/app/oracle/oradata/goolen/goolen01.dbf (5)
Block: 172 Offsets: 8154 to 8191 Dba:0x014000ac
------------------------------------------------------------------------
c102710b 0a070837 18d4e730 1c3c02c1 0b012c01 0202c102 09e4bda0 e698afe7
8caa0106 a914
<32 bytes per line>
BBED> m /x 0fe6 offset 8156
File: /opt/app/oracle/oradata/goolen/goolen01.dbf (5)
Block: 172 Offsets: 8156 to 8191 Dba:0x014000ac
------------------------------------------------------------------------
0fe60a07 083718d4 e7301c3c 02c10b01 2c010202 c10209e4 bda0e698 afe78caa
0106a914
<32 bytes per line>
BBED> m /x 8891 offset 8158
File: /opt/app/oracle/oradata/goolen/goolen01.dbf (5)
Block: 172 Offsets: 8158 to 8191 Dba:0x014000ac
------------------------------------------------------------------------
88910837 18d4e730 1c3c02c1 0b012c01 0202c102 09e4bda0 e698afe7 8caa0106
a914
<32 bytes per line>
BBED> m /x e698 offset 8160
File: /opt/app/oracle/oradata/goolen/goolen01.dbf (5)
Block: 172 Offsets: 8160 to 8191 Dba:0x014000ac
------------------------------------------------------------------------
e69818d4 e7301c3c 02c10b01 2c010202 c10209e4 bda0e698 afe78caa 0106a914
<32 bytes per line>
BBED> m /x afe7 offset 8162
File: /opt/app/oracle/oradata/goolen/goolen01.dbf (5)
Block: 172 Offsets: 8162 to 8191 Dba:0x014000ac
------------------------------------------------------------------------
afe7e730 1c3c02c1 0b012c01 0202c102 09e4bda0 e698afe7 8caa0106 a914
<32 bytes per line>
BBED> m /x 8caa offset 8164
File: /opt/app/oracle/oradata/goolen/goolen01.dbf (5)
Block: 172 Offsets: 8164 to 8191 Dba:0x014000ac
------------------------------------------------------------------------
8caa1c3c 02c10b01 2c010202 c10209e4 bda0e698 afe78caa 0106a914
<32 bytes per line>
BBED> m /x e689 offset 8166
File: /opt/app/oracle/oradata/goolen/goolen01.dbf (5)
Block: 172 Offsets: 8166 to 8191 Dba:0x014000ac
------------------------------------------------------------------------
e68902c1 0b012c01 0202c102 09e4bda0 e698afe7 8caa0106 a914
<32 bytes per line>
BBED> m /x 8de6 offset 8168
File: /opt/app/oracle/oradata/goolen/goolen01.dbf (5)
Block: 172 Offsets: 8168 to 8191 Dba:0x014000ac
------------------------------------------------------------------------
8de60b01 2c010202 c10209e4 bda0e698 afe78caa 0106a914
<32 bytes per line>
BBED> m /x 80aa offset 8170
File: /opt/app/oracle/oradata/goolen/goolen01.dbf (5)
Block: 172 Offsets: 8170 to 8191 Dba:0x014000ac
------------------------------------------------------------------------
80aa2c01 0202c102 09e4bda0 e698afe7 8caa0106 a914
<32 bytes per line>
++++数据已经修改完毕
接下来我们还得修改一个值,因为我们把数据往前移动了,所以还得修改kdbr指针,使其指向新修改的数据所在的offset,这里是8150
我们查看原来这一行数据的指针,发现指到了offset 8072,而这一行数据应该是在offset 8172,然后我查了别的数据,发现也是如此
我这里先不去研究为什么会这样
BBED> p kdbr
sb2 kdbr[0] @118 8072
BBED> d /v offset 118 count 16
File: /opt/app/oracle/oradata/goolen/goolen01.dbf (5)
Block: 172 Offsets: 118 to 133 Dba:0x014000ac
-------------------------------------------------------
881f421f 151fe91e bc1e901e 631e351e l ?.B...鮼...c.5.
<16 bytes per line>
+++++我们要把这个指针指向新的数据所在的offset,这里为8150,减去100为8050
SQL> select to_char(8050,'xxxxxxxx') from dual;
TO_CHAR(8
---------
1f72
BBED> m /x 721f offset 118
File: /opt/app/oracle/oradata/goolen/goolen01.dbf (5)
Block: 172 Offsets: 118 to 133 Dba:0x014000ac
------------------------------------------------------------------------
721f421f 151fe91e bc1e901e 631e351e
<32 bytes per line>
BBED> p kdbr
sb2 kdbr[0] @118 8050
BBED> sum apply
Check value for File 5, Block 172:
current = 0x10df, required = 0x10df
BBED> verify
DBVERIFY - Verification starting
FILE = /opt/app/oracle/oradata/goolen/goolen01.dbf
BLOCK = 172
Block Checking: DBA = 20971692, Block Type = KTB-managed data block
data header at 0x7fa9a2b82264
kdbchk: bad row offset slot 0 offs 8050 fseo 8072 dtl 8168 bhs 80
Block 172 failed with check code 6135
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED
++++我们来查一下数据:
SQL> alter system flush buffer_cache;
System altered.
SQL> select * from scott.goolen;
no rows selected
发现没有数据,这一行数据消失了
我们查一下kdbhfseo值,这个值表示free space end offset,如果数据所在的offset小于这个值,那么查询会查不到这一行数据
BBED> p kdbh.kdbhfseo
sb2 kdbhfseo @108 8070
果然,我们修改的指针指向8050,这里kdbhfseo为8070,所以查不到数据,我们把kdbhfseo值改为8000
SQL> select to_char(8000,'xxxxxxxx') from dual;
TO_CHAR(8
---------
1f40
BBED> m /x 401f offset 108
File: /opt/app/oracle/oradata/goolen/goolen01.dbf (5)
Block: 172 Offsets: 108 to 123 Dba:0x014000ac
------------------------------------------------------------------------
401f741f 741f0000 0100721f 421f151f
BBED> p kdbh.kdbhfseo
sb2 kdbhfseo @108 8000
BBED> sum apply;
Check value for File 5, Block 172:
current = 0x1017, required = 0x1017
++++我们再次查询数据看看:
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system flush buffer_cache;
System altered.
SQL> select * from scott.goolen;
ID NAME
---------- --------------------------------------------------
1 我是猪才怪
+++我们发现数据已经被修改为“我是猪才怪”