oracle查询数据替换格式,Oracle BBED修改数据块中的数据

BBED修改数据：

----

**建立测试数据：**

SQL> create table dbhang (id number,name varchar2(20));

Table created.

SQL> insert into dbhang values(1,'baoyuhang');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dbhang;

ID NAME

1 baoyuhang

**查看文件号，块号，行号：**

select

rowid,

dbms_rowid.rowid_relative_fno(rowid)rel_fno,

dbms_rowid.rowid_block_number(rowid)blockno,

dbms_rowid.rowid_row_number(rowid) rowno

from dbhang;

ROWID REL_FNO BLOCKNO ROWNO

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

AAAVoxAABAAAW8BAAA 1 93953 0

**查询数据文件：**

SQL> select file# || ' ' ||name || '' || bytes from v$datafile;

​ FILE#||''||NAME||''||BYTES

1 /oracle/app/oradata/prod/system01.dbf786432000

2 /oracle/app/oradata/prod/sysaux01.dbf545259520

3 /oracle/app/oradata/prod/undotbs01.dbf94371840

4 /oracle/app/oradata/prod/users01.dbf5242880

5 /oracle/app/oradata/prod/example01.dbf328335360

6 /oracle/app/oradata/prod/dbhang01.dbf52428800

7 /oracle/app/oradata/prod/dbhang02.dbf52428800

**将以上查询的信息保存在文本中：**

[oracle@server1 ~]$ cat datafile.txt

1 /oracle/app/oradata/prod/system01.dbf 786432000

2 /oracle/app/oradata/prod/sysaux01.dbf 545259520

3 /oracle/app/oradata/prod/undotbs01.dbf 94371840

4 /oracle/app/oradata/prod/users01.dbf 5242880

5 /oracle/app/oradata/prod/example01.dbf 328335360

6 /oracle/app/oradata/prod/dbhang01.dbf 52428800

7 /oracle/app/oradata/prod/dbhang02.dbf 52428800

**关闭数据库：**

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

**创建BBED参数文件：**

[oracle@server1 ~]$ cat bbed.par

blocksize=8192

listfile=/home/oracle/datafile.txt //指定文本文件

mode=edit //编辑模式

**现将数据baoyuhang修改为bbbyuhang**

**连接BBED，进行数据定位：**

[oracle@server1 ~]$ bbed parfile=/home/oracle/bbed.par

Password:

BBED: Release 2.0.0.0.0 - Limited Production on Thu Jul 30 05:59:45 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED>

**指定1号文件93953号块：**

BBED> set dba 1,93953 offset 0

DBA 0x00416f01 (4288257 1,93953)

OFFSET 0

**定位数据：(确定数据在block中的偏移量)**

BBED> find /c baoyuhang

File: /oracle/app/oradata/prod/system01.dbf (1)

Block: 93953 **Offsets: 8179 to 8191** Dba:0x00416f01

62616f79 7568616e 6704066c 38

**dump查看一下offset 8179信息：**

BBED> dump /v dba 1,93953 offset 8179 count 128

File: /oracle/app/oradata/prod/system01.dbf (1)

Block: 93953 Offsets: 8179 to 8191 Dba:0x00416f01

62616f79 7568616e 6704066c 38 l baoyuhang..l8

**Offsets: 8179 to 8191** 代表这一行的地址。

**也是说baoyuhang属于8179-8191 **

b:8179

a:8180

o:8181

**修改 block将bao替换为bbb：**

BBED> **modify /c bbb dba 1,93953 offset 8179**

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

File: /oracle/app/oradata/prod/system01.dbf (1)

Block: 93953 Offsets: 8179 to 8191 Dba:0x00416f01

62626279 7568616e 670106ba 3c

**再次查看修改后的数据：**

BBED> dump /v dba 1,93953 offset 8179 count 128

File: /oracle/app/oradata/prod/system01.dbf (1)

Block: 93953 Offsets: 8179 to 8191 Dba:0x00416f01

62626279 7568616e 6704066c 38 l **bbbyuhang**..l8

**应用变更：**

BBED> sum dba 1,93953 apply

Check value for File 1, Block 93953:

current = 0x29a2, required = 0x29a2

**回到sqlplus查询修改后的数据**

SQL> startup;

ORACLE instance started.

Total System Global Area 830930944 bytes

Fixed Size 2257800 bytes

Variable Size 541068408 bytes

Database Buffers 281018368 bytes

Redo Buffers 6586368 bytes

Database mounted.

Database opened.

SQL> select * from dbhang;

ID NAME

1 bbbyuhang

**修改完毕.**

BBED的安装：http://blog.itpub.net/69975956/viewspace-2707944/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/69975956/viewspace-2707945/，如需转载，请注明出处，否则将追究法律责任。