联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
我们都知道，根据oracle 事务的一致性,当我们在session 1中进行dml操作,如果未提交在其他会话中是无法看到修改后的值(只能看到修改前的值).这里通过bbed模拟部分提交事务从而实现在其他会话中查看到另外会话未提交事务(本质已经部分模拟提交,还有undo segment header中信息未清理,下篇补充)
create table(session 1)
SQL> create table chf.t_xifenfei
2 as
3 select object_id,object_name from dba_objects where rownum<20;
Table created.
SQL> select rowid,
2 dbms_rowid.rowid_relative_fno(rowid) rel_fno,
3 dbms_rowid.rowid_block_number(rowid) block_no
4 from chf.t_xifenfei;
ROWID REL_FNO BLOCK_NO
------------------ ---------- ----------
AAANL3AAEAAAAAcAAA 4 28
AAANL3AAEAAAAAcAAB 4 28
AAANL3AAEAAAAAcAAC 4 28
AAANL3AAEAAAAAcAAD 4 28
AAANL3AAEAAAAAcAAE 4 28
AAANL3AAEAAAAAcAAF 4 28
AAANL3AAEAAAAAcAAG 4 28
AAANL3AAEAAAAAcAAH 4 28
AAANL3AAEAAAAAcAAI 4 28
AAANL3AAEAAAAAcAAJ 4 28
AAANL3AAEAAAAAcAAK 4 28
AAANL3AAEAAAAAcAAL 4 28
AAANL3AAEAAAAAcAAM 4 28
AAANL3AAEAAAAAcAAN 4 28
AAANL3AAEAAAAAcAAO 4 28
AAANL3AAEAAAAAcAAP 4 28
AAANL3AAEAAAAAcAAQ 4 28
AAANL3AAEAAAAAcAAR 4 28
AAANL3AAEAAAAAcAAS 4 28
19 rows selected.
SQL> select * from chf.t_xifenfei;
OBJECT_ID OBJECT_NAME
---------- --------------------
20 ICOL$
44 I_USER1
28 CON$
15 UNDO$
29 C_COBJ#
3 I_OBJ#
25 PROXY_ROLE_DATA$
39 I_IND1
51 I_CDEF2
26 I_PROXY_ROLE_DATA$_1
17 FILE$
13 UET$
9 I_FILE#_BLOCK#
41 I_FILE1
48 I_CON1
38 I_OBJ3
7 I_TS#
53 I_CDEF4
19 IND$
19 rows selected.
dump block(session 2)
SQL> alter system checkpoint; System altered. SQL> alter system dump datafile 4 block 28; System altered. Itl Xid Uba Flag Lck Scn/Fsc 0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0b2c.c02d1987 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
update record(session 1)
SQL> update chf.t_xifenfei set object_name ='www.xifenfei.com' where rownum<15;
14 rows updated.
SQL> col object_name for a20
SQL> select * from chf.t_xifenfei;
OBJECT_ID OBJECT_NAME
---------- --------------------
20 www.xifenfei.com
44 www.xifenfei.com
28 www.xifenfei.com
15 www.xifenfei.com
29 www.xifenfei.com
3 www.xifenfei.com
25 www.xifenfei.com
39 www.xifenfei.com
51 www.xifenfei.com
26 www.xifenfei.com
17 www.xifenfei.com
13 www.xifenfei.com
9 www.xifenfei.com
41 www.xifenfei.com
48 I_CON1
38 I_OBJ3
7 I_TS#
53 I_CDEF4
19 IND$
19 rows selected.
dump block(session 2)
SQL> alter system checkpoint; System altered. --注意flush buffer_cache(不然后面bbed修改会被不能通过select显示,而且会被覆盖) SQL> alter system flush buffer_cache; System altered. SQL> alter system flush shared_pool; System altered. SQL> alter system dump datafile 4 block 28; System altered. Itl Xid Uba Flag Lck Scn/Fsc 0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0b2c.c02d1987 0x02 0x000a.00a.0000017e 0x0081ffc7.01a2.22 ---- 14 fsc 0x0000.00000000 <--注意Lck 14 0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bbed commit Transaction(session 3)
BBED> p ktbbh
struct ktbbh, 96 bytes @20
ub1 ktbbhtyp @20 0x01 (KDDBTDATA)
union ktbbhsid, 4 bytes @24
ub4 ktbbhsg1 @24 0x0000d2f7
ub4 ktbbhod1 @24 0x0000d2f7
struct ktbbhcsc, 8 bytes @28
ub4 kscnbas @28 0xc02d1aec
ub2 kscnwrp @32 0x0b2c
b2 ktbbhict @36 3
ub1 ktbbhflg @38 0x32 (NONE)
ub1 ktbbhfsl @39 0x00
ub4 ktbbhfnx @40 0x01000019
struct ktbbhitl[0], 24 bytes @44
struct ktbitxid, 8 bytes @44
ub2 kxidusn @44 0xffff
ub2 kxidslt @46 0x0000
ub4 kxidsqn @48 0x00000000
struct ktbituba, 8 bytes @52
ub4 kubadba @52 0x00000000
ub2 kubaseq @56 0x0000
ub1 kubarec @58 0x00
ub2 ktbitflg @60 0x8000 (KTBFCOM)
union _ktbitun, 2 bytes @62
b2 _ktbitfsc @62 2860
ub2 _ktbitwrp @62 0x0b2c
ub4 ktbitbas @64 0xc02d1987
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x000a
ub2 kxidslt @70 0x000a
ub4 kxidsqn @72 0x0000017e
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x0081ffc7
ub2 kubaseq @80 0x01a2
ub1 kubarec @82 0x22
ub2 ktbitflg @84 0x000e (NONE) <--修改要改为8000
union _ktbitun, 2 bytes @86
b2 _ktbitfsc @86 0
ub2 _ktbitwrp @86 0x0000
ub4 ktbitbas @88 0x00000000
struct ktbbhitl[2], 24 bytes @92
struct ktbitxid, 8 bytes @92
ub2 kxidusn @92 0x0000
ub2 kxidslt @94 0x0000
ub4 kxidsqn @96 0x00000000
struct ktbituba, 8 bytes @100
ub4 kubadba @100 0x00000000
ub2 kubaseq @104 0x0000
ub1 kubarec @106 0x00
ub2 ktbitflg @108 0x0000 (NONE)
union _ktbitun, 2 bytes @110
b2 _ktbitfsc @110 0
ub2 _ktbitwrp @110 0x0000
ub4 ktbitbas @112 0x00000000
BBED> m /x 0080 offset 84
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/oracle/oradata/XFF/users01.dbf (0)
Block: 28 Offsets: 84 to 115 Dba:0x00000000
------------------------------------------------------------------------
00800000 00000000 00000000 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 0, Block 28:
current = 0x03dc, required = 0x03dc
BBED> p ktbbh
struct ktbbh, 96 bytes @20
…………
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x000a
ub2 kxidslt @70 0x000a
ub4 kxidsqn @72 0x0000017e
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x0081ffc7
ub2 kubaseq @80 0x01a2
ub1 kubarec @82 0x22
ub2 ktbitflg @84 0x8000 (KTBFCOM) <--修改值
union _ktbitun, 2 bytes @86
b2 _ktbitfsc @86 0
ub2 _ktbitwrp @86 0x0000
ub4 ktbitbas @88 0x00000000
…………
dump block(session 2)
SQL> alter system dump datafile 4 block 28; System altered. Itl Xid Uba Flag Lck Scn/Fsc 0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0b2c.c02d1987 0x02 0x000a.00a.0000017e 0x0081ffc7.01a2.22 C--- 0 scn 0x0000.00000000 0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
select data(session 4)
SQL> select object_id,object_name from chf.t_xifenfei;
OBJECT_ID OBJECT_NAME
---------- --------------------
20 www.xifenfei.com
44 www.xifenfei.com
28 www.xifenfei.com
15 www.xifenfei.com
29 www.xifenfei.com
3 www.xifenfei.com
25 www.xifenfei.com
39 www.xifenfei.com
51 www.xifenfei.com
26 www.xifenfei.com
17 www.xifenfei.com
13 www.xifenfei.com
9 www.xifenfei.com
41 www.xifenfei.com
48 I_CON1
38 I_OBJ3
7 I_TS#
53 I_CDEF4
19 IND$
19 rows selected.
这里可以看到,已经模拟出来在其他session中可以访问数据库为commit的记录(在该block级别已经模拟了commit)
请问飞总，这个模拟提交后，如果db 重启，事物会不会被回滚呢？
这个不一定，具体看情况，如果有些情况下，block可能会被buffer cache中的block重写回去