06 使用bbed提交delete的数据--01
使用bbed模拟delete提交操作
--session 1
[email protected] orcl >create table tt(id int,name varchar2(100)); Table created. [email protected] orcl >insert into tt values(1,'AAAAA'); 1 row created. [email protected] orcl > insert into tt values(2,'BBBBB'); 1 row created. [email protected] orcl >insert into tt values(3,'CCCCC'); 1 row created. [email protected] orcl >COMMIT; Commit complete. [email protected] orcl > alter system flush buffer_cache; System altered. [email protected] orcl > select rowid,id,name,DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) file#,DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) block# from test.tt; ROWID ID NAME FILE# BLOCK# ------------------ ---------- -------------------- ---------- ---------- AAAVW0AAFAAAAi3AAA 1 AAAAA 5 2231 AAAVW0AAFAAAAi3AAB 2 BBBBB 5 2231 AAAVW0AAFAAAAi3AAC 3 CCCCC 5 2231 [email protected] orcl >alter system checkpoint; System altered. [email protected] orcl > alter system dump datafile 5 block 2231; System altered. [email protected] orcl >select * from v$diag_info where name='Default Trace File'; INST_ID NAME VALUE ---------- -------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 Default Trace File /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_18408.trc [email protected] orcl >select * from tt; ID NAME ---------- -------------------- 1 AAAAA 2 BBBBB 3 CCCCC
dump的结果
--session 1执行删除操作
[email protected] orcl >delete from tt where name='AAAAA'; 1 row deleted. [email protected] orcl > select * from tt; ID NAME ---------- -------------------- 2 BBBBB 3 CCCCC
--session2执行操作
SQL> alter system checkpoint; System altered. SQL> alter system flush buffer_cache; System altered. SQL> alter system flush shared_pool; System altered. SQL> alter system dump datafile 5 block 2231; System altered. SQL> select * from v$diag_info where name='Default Trace File'; INST_ID NAME ---------- ---------------------------------------------------------------- VALUE -------------------------------------------------------------------------------- 1 Default Trace File /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_22529.trc SQL> alter system dump datafile 5 block 2231; System altered. SQL> select * from v$diag_info where name='Default Trace File'; INST_ID NAME ---------- ---------------------------------------------------------------- VALUE -------------------------------------------------------------------------------- 1 Default Trace File /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_22529.trc
dump结果
--session 3 bbed会话
BBED> set file 5 block 2231 FILE# 5 BLOCK# 2231 BBED> p ktbbh struct ktbbh, 72 bytes @20 ub1 ktbbhtyp @20 0x01 (KDDBTDATA) union ktbbhsid, 4 bytes @24 ub4 ktbbhsg1 @24 0x000155b4 ub4 ktbbhod1 @24 0x000155b4 struct ktbbhcsc, 8 bytes @28 ub4 kscnbas @28 0x0092cb9b ub2 kscnwrp @32 0x0000 sb2 ktbbhict @36 2 ub1 ktbbhflg @38 0x32 (NONE) ub1 ktbbhfsl @39 0x00 ub4 ktbbhfnx @40 0x014008b0 struct ktbbhitl[0], 24 bytes @44 struct ktbitxid, 8 bytes @44 ub2 kxidusn @44 0x000a ub2 kxidslt @46 0x000b ub4 kxidsqn @48 0x00004456 struct ktbituba, 8 bytes @52 ub4 kubadba @52 0x00c002d8 ub2 kubaseq @56 0x0175 ub1 kubarec @58 0x08 ub2 ktbitflg @60 0x8000 (KTBFCOM) union _ktbitun, 2 bytes @62 sb2 _ktbitfsc @62 0 ub2 _ktbitwrp @62 0x0000 ub4 ktbitbas @64 0x0092cb3d struct ktbbhitl[1], 24 bytes @68 struct ktbitxid, 8 bytes @68 ub2 kxidusn @68 0x0008 ub2 kxidslt @70 0x0011 ub4 kxidsqn @72 0x000078b4 struct ktbituba, 8 bytes @76 ub4 kubadba @76 0x00c00397 ub2 kubaseq @80 0x017e ub1 kubarec @82 0x1d ub2 ktbitflg @84 0x0001 (NONE) union _ktbitun, 2 bytes @86 sb2 _ktbitfsc @86 10 ub2 _ktbitwrp @86 0x000a ub4 ktbitbas @88 0x00000000
bbed执行修改
BBED> m /x 0080 offset 84 Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y File: /u01/app/oracle/oradata/orcl/test01.dbf (5) Block: 2231 Offsets: 84 to 595 Dba:0x014008b7 ------------------------------------------------------------------------ 00800a00 00000000 00000000 00000000 00010300 ffff1800 741f5c1f 681f0000 03008c1f 801f741f c21db41e 770b6e0c 480d240e 1d0f2510 21110312 1513f013 <32 bytes per line> BBED> sum apply Check value for File 5, Block 2231: current = 0x1761, required = 0x1761
再次查看
BBED> p ktbbh struct ktbbh, 72 bytes @20 ub1 ktbbhtyp @20 0x01 (KDDBTDATA) union ktbbhsid, 4 bytes @24 ub4 ktbbhsg1 @24 0x000155b4 ub4 ktbbhod1 @24 0x000155b4 struct ktbbhcsc, 8 bytes @28 ub4 kscnbas @28 0x0092cb9b ub2 kscnwrp @32 0x0000 sb2 ktbbhict @36 2 ub1 ktbbhflg @38 0x32 (NONE) ub1 ktbbhfsl @39 0x00 ub4 ktbbhfnx @40 0x014008b0 struct ktbbhitl[0], 24 bytes @44 struct ktbitxid, 8 bytes @44 ub2 kxidusn @44 0x000a ub2 kxidslt @46 0x000b ub4 kxidsqn @48 0x00004456 struct ktbituba, 8 bytes @52 ub4 kubadba @52 0x00c002d8 ub2 kubaseq @56 0x0175 ub1 kubarec @58 0x08 ub2 ktbitflg @60 0x8000 (KTBFCOM) union _ktbitun, 2 bytes @62 sb2 _ktbitfsc @62 0 ub2 _ktbitwrp @62 0x0000 ub4 ktbitbas @64 0x0092cb3d struct ktbbhitl[1], 24 bytes @68 struct ktbitxid, 8 bytes @68 ub2 kxidusn @68 0x0008 ub2 kxidslt @70 0x0011 ub4 kxidsqn @72 0x000078b4 struct ktbituba, 8 bytes @76 ub4 kubadba @76 0x00c00397 ub2 kubaseq @80 0x017e ub1 kubarec @82 0x1d ub2 ktbitflg @84 0x8000 (KTBFCOM) union _ktbitun, 2 bytes @86 sb2 _ktbitfsc @86 10 ub2 _ktbitwrp @86 0x000a ub4 ktbitbas @88 0x00000000
BBED> p *kdbr[0] rowdata[24] ----------- ub1 rowdata[24] @8176 0x3c ###状态为3c，标记已经删除 BBED> x/rncccccc rowdata[24] @8176 ----------- flag@8176: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH) lock@8177: 0x02 cols@8178: 0
BBED> dump /v offset 8176 count 20 File: /u01/app/oracle/oradata/orcl/test01.dbf (5) Block: 2231 Offsets: 8176 to 8191 Dba:0x014008b7 ------------------------------------------------------- 3c020202 c1020541 41414141 01069bcb l <...????.AAAAA... <16 bytes per line>
--sessino 2 再次执行dumpfile
SQL> alter system dump datafile 5 block 2231; System altered. SQL> select * from v$diag_info where name='Default Trace File'; INST_ID NAME ---------- ---------------------------------------------------------------- VALUE -------------------------------------------------------------------------------- 1 Default Trace File /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_22529.trc
--这里的状态已经显示为commit
再开session 查询表tt的数据
---发现别的session会话还在~~~~~貌似修改没起到作用？ 但是dump file已经显示为commit了啊~~~~~不解
有参考帖子