bbed-修改表数据

关于bbed这里不做介绍，起功能很强大，直接进行修改数据

一、安装bbed

10g以前版本不用上传库文件，更高版本需要上传库文件

上传ssbbded.o和sbbdpt.o 到$ORACLE_HOME/rdbms/lib下

[oracle@renCAP lib]$ make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed

[oracle@renCAP lib]$ cp bbed ../../bin/



[oracle@renCAP lib]$ ./bbed

Message 112 not found; No message file for product=RDBMS, facility=BBED

BBED-00113: file not found



[oracle@renCAP mesg]$ scp bbedus.msb oracle@10.140.100.15:/opt/app/oracle/product/12.1.0/db_1/rdbms/mesg/



默认密码:blockedit



[oracle@renCAP ~]$ bbed

Password:



BBED: Release 2.0.0.0.0 - Limited Production on Wed Jun 19 19:05:23 2019



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



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



BBED>

Ok 登陆成功

二、bbed修改表数据

1、准备环境

[oracle@renCAP ~]$ sqlplus / as sysdba



SQL*Plus: Release 12.2.0.1.0 Production on Wed Jun 19 19:09:21 2019



Copyright (c) 1982, 2016, Oracle. All rights reserved.





Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production



SQL> show pdbs



CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 RENPDB READ WRITE NO

SQL> alter session set container=renpdb;



Session altered.



SQL> show pdbs



CON_ID CON_NAME OPEN MODE RESTRICTED

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

3 RENPDB READ WRITE NO



SQL> create tablespace bbed_tbs datafile size 100m;



Tablespace created.



SQL> create user bbed_user identified by oracle default tablespace bbed_tbs;



User created.



SQL> grant dba to bbed_user;



Grant succeeded.



[oracle@renCAP ~]$ sqlplus bbed_user/oracle@RENPDB



SQL*Plus: Release 12.2.0.1.0 Production on Wed Jun 19 19:20:10 2019



Copyright (c) 1982, 2016, Oracle. All rights reserved.





Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production



SQL> show user

USER is "BBED_USER"



SQL> create table bbed_tab (id int,name varchar2(20)) tablespace bbed_tbs;



Table created.



SQL> insert into bbed_tab values(1,'zhangsan');



1 row created.



SQL> insert into bbed_tab values(2,'lisi');



1 row created.



SQL> insert into bbed_tab values(3,'wangwu');



1 row created.



SQL> commit;



Commit complete.



SQL> select * from bbed_tab;



ID NAME

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

1 zhangsan

2 lisi

3 wangwu

2、bbed修改数据

自定义编写两个参数文件

select file#||' '||name||' '||bytes as flist from v$datafile ;

[oracle@renCAP bbed]$ cat par.bbed

blocksize=8192

listfile=bbedfile.log

mode=edit

[oracle@renCAP bbed]$

[oracle@renCAP bbed]$

[oracle@renCAP bbed]$ cat bbedfile.log

9 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_system_gjltqowo_.dbf 262144000

10 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_sysaux_gjltqp14_.dbf 387973120

11 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_undotbs1_gjltqp16_.dbf 104857600

12 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_users_gjlttzro_.dbf 5242880

14 /data/cap_test.dbf 1073741824

15 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_bbed_tbs_gjn66twm_.dbf 104857600



登陆成功：

[oracle@renCAP bbed]$ bbed parfile=par.bbed

Password:



BBED: Release 2.0.0.0.0 - Limited Production on Wed Jun 19 19:31:17 2019



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



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



BBED>

--开始修改数据

select t.*,

dbms_rowid.rowid_relative_fno(t.rowid) as "FNO#",

dbms_rowid.rowid_block_number(t.rowid) as "BLK#",

dbms_rowid.rowid_row_number(t.rowid) as "ROW#"

from bbed_user.bbed_tab t;

SQL> select t.*,

2 dbms_rowid.rowid_relative_fno(t.rowid) as "FNO#",

3 dbms_rowid.rowid_block_number(t.rowid) as "BLK#",

4 dbms_rowid.rowid_row_number(t.rowid) as "ROW#"

5 from bbed_user.bbed_tab t;



ID NAME FNO# BLK# ROW#

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

1 zhangsan 15 142 0

2 lisi 15 142 1

3 wangwu 15 142 2

--要修改的数据‘lisi’在15号文件142号块

SQL> select dump('lisi',16) from dual;



DUMP('LISI',16)

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

Typ=96 Len=4: 6c,69,73,69

SELECT COUNT(*) COUNTS,

MAX(DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)) MAX_ROWNUM,

MIN(DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)) MIN_ROWNUM

FROM bbed_user.bbed_tab

WHERE DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) = 15

AND DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) = 142;

在15号文件142块一共有三条数据，最大号是2，最小是0

SQL> SELECT COUNT(*) COUNTS,

2 MAX(DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)) MAX_ROWNUM,

3 MIN(DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)) MIN_ROWNUM

4 FROM bbed_user.bbed_tab

5 WHERE DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) = 15

6 AND DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) = 142;



COUNTS MAX_ROWNUM MIN_ROWNUM

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

3            2          0

bbed改数据

BBED> info

File# Name Size(blks)

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

9 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_ 32000

10 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_ 47360

11 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_ 12800

12 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_ 640

14 /data/cap_test.dbf 131072

15 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_ 12800

BBED> set file 15 block 142;

FILE# 15

BLOCK# 142

BBED> map
 File: /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_bbed_tbs_gjn (15)
 Block: 142                                   Dba:0x03c0008e
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0       

 struct ktbbh, 72 bytes                     @20      

 struct kdbh, 14 bytes                      @100     

 struct kdbt[1], 4 bytes                    @114     

 sb2 kdbr[3]                                @118     

 ub1 freespace[8025]                        @124     

 ub1 rowdata[39]                            @8149    

 ub4 tailchk                                @8188    

BBED> f /c lisi

File: /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_bbed_tbs_gjn (15)

Block: 142 Offsets: 8169 to 8191 Dba:0x03c0008e

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

6c697369 2c010202 c102087a 68616e67 73616e01 06babe

<32 bytes per line>

BBED> set offset 8169;

OFFSET 8169

SQL> select dump('cap',16) from dual;

DUMP('CAP',16)

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

Typ=96 Len=3: 63,61,70

BBED> m /x 63617000

File: /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_bbed_tbs_gjn (15)

Block: 142 Offsets: 8169 to 8191 Dba:0x03c0008e

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

63617000 2c010202 c102087a 68616e67 73616e01 06babe

<32 bytes per line>

BBED> sum apply

Check value for File 15, Block 142:

current = 0x2617, required = 0x2617

BBED> d /v file 15 block 142 offset 8169

File: /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_bbed_tbs_gjn (15)

Block: 142 Offsets: 8169 to 8191 Dba:0x03c0008e

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

63617000 2c010202 c102087a 68616e67 l cap.,...hang

73616e01 06babe l san..

<16 bytes per line>

--查看数据已改成CAP

SQL> alter system flush buffer_cache;

System altered.

SQL> select * from bbed_tab;

ID NAME

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

1 zhangsan

2 cap

3 wangwu

--end