前面幾篇文章介紹了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

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

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

比如要修改empno=7521的員工名字 WARD，首先找到次記錄在資料塊中的地址：dba 4，396

SQL> select dbms_rowid.ROWID_OBJECT(rowid) data_object_id#,

2 dbms_rowid.ROWID_RELATIVE_FNO(rowid) rfile#,

3 dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block#,

4 dbms_rowid.ROWID_ROW_NUMBER(rowid) row#,

5 rowid

6 from yangobj where empno=7521;

DATA_OBJECT_ID# RFILE# BLOCK# ROW# ROWID

--------------- ---------- ---------- ---------- ------------------

52526 4 396 2 AAAM0uAAEAAAAGMAAC

關閉資料庫，使用bbed進行資料塊的修改，也可以不關閉，不過要進行

alter sysetm flush shared_pool;

alter sysetm flush buffer_cache;

清理快取中以前查詢的資料。

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

使用BBED 進行修改：

定位到 dba 4，396

BBED> set dba 4,396

DBA 0x0100018c (16777612 4,396)

OFFSET 8072

找到WARD 的offset 為 8072

BBED> find /c WARD

File: /opt/oracle/oradata/orcl/users01.dbf (4)

Block: 396 Offsets: 8072 to 8135 Dba:0x0100018c

------------------------------------------------------------------------

57415244 0853414c 45534d41 4e03c24d 630777b5 02160101 0103c20d 3302c206

02c11f2c 000803c2 4b640541 4c4c454e 0853414c 45534d41 4e03c24d 630777b5

<32 bytes per line>

修改WARD為yang，注意如果要修改的字串中字元的個數。如果是varchar2 型別的，修改前與修改後要一致；如果是char型別的，修改後的資料不能超過定義時char(N)中N的數值。（文章結尾有一個例子）

BBED> modify /c yang dba 4,396 offset 8072

File: /opt/oracle/oradata/orcl/users01.dbf (4)

Block: 396 Offsets: 8072 to 8135 Dba:0x0100018c

------------------------------------------------------------------------

79616e67 0853414c 45534d41 4e03c24d 630777b5 02160101 0103c20d 3302c206

02c11f2c 000803c2 4b640541 4c4c454e 0853414c 45534d41 4e03c24d 630777b5

<32 bytes per line>

使用dump命令檢視結果

BBED> dump /v dba 4,396 offset 8072

File: /opt/oracle/oradata/orcl/users01.dbf (4)

Block: 396 Offsets: 8072 to 8135 Dba:0x0100018c

-------------------------------------------------------

79616e67 0853414c 45534d41 4e03c24d l yang.SALESMAN..M

630777b5 02160101 0103c20d 3302c206 l c.w.........3...

02c11f2c 000803c2 4b640541 4c4c454e l ...,....Kd.ALLEN

0853414c 45534d41 4e03c24d 630777b5 l .SALESMAN..Mc.w.

<16 bytes per line>

檢視block 4，396的校驗

BBED> sum dba 4,396

Check value for File 4, Block 396:

current = 0x27cd, required = 0x24df

current 與reqired 必須一致。

BBED> sum dba 4,396 apply

Check value for File 4, Block 396:

current = 0x24df, required = 0x24df

重新啟動資料庫，sqlplus 中進行驗證。。

SQL> startup

ORACLE instance started.

Total System Global Area 1224736768 bytes

Fixed Size 2020384 bytes

Variable Size 318770144 bytes

Database Buffers 889192448 bytes

Redo Buffers 14753792 bytes

Database mounted.

Database opened.

SQL> conn yang/yang

Connected.

SQL> col ename for a15

SQL> col job for a10

SQL> set linesize 120

SQL> set pagesize 1000

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 yang SALESMAN 7698 22-FEB-81 1250 500 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

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

SQL> select * from yangobj where empno=7521;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- --------------- ---------- ---------- ------------------ ---------- ---------- ----------

7521 yang SALESMAN 7698 22-FEB-81 1250 500 30

SQL>

附上一個例子：

修改varchar2型別資料原資料與新資料長度不一導致查詢結果中其他欄位值也被修改了。

SQL> ---修改後查詢

SQL> select * from yangobj where empno=7521;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- ---------- ---------- ------------------ ---------- ---------- ----------

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

SQL> alter system flush buffer_cache;

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 Yang lALESMAN翸cw? 5.5100E-56 14-, -44

?3

7566 JONES MANAGER -2.26E+125 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

【BBED】使用bbed修改字元型別資料

相關文章