bbed修改varchar数据

来源：互联网发布：河北软件学院怎么样? 编辑：程序博客网时间：2024/06/05 18:54

bbed更改data block里的varchar型数据

此实验只是为了熟悉bbed用法和data block块的结构，在实际工作中这样的修改没有太大的实际意义

SQL> show user
USER 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 我是猪才怪

+++我们发现数据已经被修改为“我是猪才怪”