bbed的使用(一) 介绍了BBED的编译安装。
bbed的使用(二) 介绍了bbed的语法规则和常用命令的使用。
bbed的使用(三) 介绍了bbed的命令。
如何修改字符类型的数据,使用bbed修改数据表中字符类型的数据
下面介绍使用bbed修改数字类型数据
yangobj表是有scott下的emp经过CTAS创建。
修改前:
SQL> select * from yangobj;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ ---------- ---------- ------------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
计划对WARD的comm值500修改为800.
因为使用bbed修改数据时最好关闭数据库。所以从另外的数据库中查询800,500在数据库中的存储。
yang@rac1>select dump(800,1016) FROM DUAL;
DUMP(800,1016)
-----------------
Typ=2 Len=2: c2,9
yang@rac1>select dump(500,1016) FROM DUAL;
DUMP(500,1016)
-----------------
Typ=2 Len=2: c2,6
使用bbed对数据
使用p 命令查看杭记录
BBED> p kdbr
sb2 kdbr[0] @142 8026
sb2 kdbr[1] @144 7983
sb2 kdbr[2] @146 7940
sb2 kdbr[3] @148 7899
sb2 kdbr[4] @150 7854
sb2 kdbr[5] @152 7813
sb2 kdbr[6] @154 7772
sb2 kdbr[7] @156 7732
sb2 kdbr[8] @158 7694
sb2 kdbr[9] @160 7651
sb2 kdbr[10] @162 7613
sb2 kdbr[11] @164 7575
sb2 kdbr[12] @166 7536
sb2 kdbr[13] @168 7497
查看第三行WARD的数据。
BBED> p *kdbr[2]
rowdata[443]
------------
ub1 rowdata[443] @8064 0x2c
BBED> x /rnccntnnn
rowdata[443] @8064
------------
flag@8064: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8065: 0x00
cols@8066: 8
col 0[3] @8067: 7521
col 1[4] @8071: WARD
col 2[8] @8076: SALESMAN
col 3[3] @8085: 7698
col 4[7] @8089: 22-FEB-81
col 5[3] @8097: 1250
col 6[2] @8101: 500
col 7[2] @8104: 30
显示数据库中存储的数据格式。
BBED> x /r
rowdata[443] @8064
------------
flag@8064: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8065: 0x00
cols@8066: 8
col 0[3] @8067: 0xc2 0x4c 0x16
col 1[4] @8071: 0x57 0x41 0x52 0x44
col 2[8] @8076: 0x53 0x41 0x4c 0x45 0x53 0x4d 0x41 0x4e
col 3[3] @8085: 0xc2 0x4d 0x63
col 4[7] @8089: 0x77 0xb5 0x02 0x16 0x01 0x01 0x01
col 5[3] @8097: 0xc2 0x0d 0x33
col 6[2] @8101: 0xc2 0x06
col 7[2] @8104: 0xc1 0x1f
BBED> set offset 8101
OFFSET 8101
BBED> dump /v offset 8101
File: /opt/oracle/oradata/orcl/users01.dbf (4)
Block: 396 Offsets: 8101 to 8191 Dba:0x0100018c
-------------------------------------------------------
02c20602 c11f2c00 0803c24b 6405414c l ......,....Kd.AL
4c454e08 53414c45 534d414e 03c24d63 l LEN.SALESMAN..Mc
0777b502 14010101 02c21102 c20402c1 l .w..............
1f2c0008 03c24a46 05534d49 54480543 l .,....JF.SMITH.C
4c45524b 03c25003 0777b40c 11010101 l LERK..P..w......
02c209ff 02c11502 06250c l .........%.
02 为列与列的间隔,所以再偏移一位(8bit)
BBED> dump /v offset 8102
File: /opt/oracle/oradata/orcl/users01.dbf (4)
Block: 396 Offsets: 8102 to 8191 Dba:0x0100018c
-------------------------------------------------------
c20602c1 1f2c0008 03c24b64 05414c4c l .....,....Kd.ALL
454e0853 414c4553 4d414e03 c24d6307 l EN.SALESMAN..Mc.
77b50214 01010102 c21102c2 0402c11f l w...............
2c000803 c24a4605 534d4954 4805434c l ,....JF.SMITH.CL
45524b03 c2500307 77b40c11 01010102 l ERK..P..w.......
c209ff02 c1150206 250c l ........%.
修改 c206-->c209
BBED> modify /x 0xc209
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /opt/oracle/oradata/orcl/users01.dbf (4)
Block: 396 Offsets: 8102 to 8191 Dba:0x0100018c
------------------------------------------------------------------------
c20902c1 1f2c0008 03c24b64 05414c4c 454e0853 414c4553 4d414e03 c24d6307
77b50214 01010102 c21102c2 0402c11f 2c000803 c24a4605 534d4954 4805434c
45524b03 c2500307 77b40c11 01010102 c209ff02 c1150206 250c
校验并应用。
BBED> sum dba 4,396
Check value for File 4, Block 396:
current = 0x27cd, required = 0x28cd
BBED> sum dba 4,396 apply
Check value for File 4, Block 396:
current = 0x28cd, required = 0x28cd
重新显示,500-->800
BBED> x /rnccntnnn
rowdata[443] @8064
------------
flag@8064: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8065: 0x00
cols@8066: 8
col 0[3] @8067: 7521
col 1[4] @8071: WARD
col 2[8] @8076: SALESMAN
col 3[3] @8085: 7698
col 4[7] @8089: 22-FEB-81
col 5[3] @8097: 1250
col 6[2] @8101: 800
col 7[2] @8104: 30
修改后,重新启动数据库查看结果:
SQL> set linesize 120
SQL> select * from yangobj;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- --------------- ---------- ---------- ------------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 800 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
14 rows selected.
注意:这里500 和800都是占有2byte,如果修改为888
yang@rac1>select dump(888,1016) FROM DUAL;
DUMP(888,1016)
--------------------
Typ=2 Len=3: c2,9,59
则占用3个byte,查询数据时会出现乱码,
如何修改字符类型的数据中有提到。