[20130312]undo与DML-delete.txt
闲着没事,研究一下undo与DML.
DML操作在UNDO中的信息
通过Dump UNDO Block观察到DML操作记录在UNDO中的信息,主要为以下内容:
1、对于Insert操作,需要在UNDO中记录插入行的ROWID.
2、对于Update操作,需要在UNDO中记录被更新列的前镜像的值,同时也会记录被更新行的ROWID。
3、对于Delete操作,需要在UNDO中记录被删除行所有列的值(前镜像)及ROWID。
1、接着前面的测试继续测试delete操作:
--先建立一个测试表,选择都是字符类型是为了显示方便.而且不建立使用,这样好分析一些.
SQL> select * from v$version where rownum
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
create table t (a varchar2(10),b varchar2(10));
insert into t values('1','a');
insert into t values('2','b');
insert into t values('3','c');
commit;
SQL> select t.rowid,t.* from t;
ROWID A B
------------------ ---------- ----------
AABB8BAAEAAAAWfAAA 1 a
AABB8BAAEAAAAWfAAB 2 b
AABB8BAAEAAAAWfAAC 3 c
SQL> @lookup_rowid AABB8BAAEAAAAWfAAA
OBJECT FILE BLOCK ROW
---------- ---------- ---------- ----------
270081 4 1439 0
2.执行删除操作不提交:
SQL> delete from t where a =2;
1 row deleted.
SQL> select dbms_transaction.local_transaction_id() x from dual ;
X
------------------------------
5.17.13807
SQL> select XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,STATUS from v$transaction;
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBAREC STATUS
---------- ---------- ---------- ---------- ---------- ---------- ----------------
5 17 13807 3 20326 3 ACTIVE
--从以上操作发现,在删除记录后UBAREC=3(0x03).
--从以上信息,可以知道在datafile 3 block 20326.
--转储看看UBAREC=3的情况:(0x03)
alter system checkpoint ;
alter system dump datafile 3 block 20326;
********************************************************************************
UNDO BLK:
xid: 0x0005.011.000035ef seq: 0x1709 cnt: 0x3 irb: 0x3 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1f70 0x02 0x1f1c 0x03 0x1e80
*-----------------------------
* Rec #0x3 slt: 0x11 objn: 270081(0x00041f01) objd: 270081 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x00c04f67.1709.0e ctl max scn: 0x0000.c01937bf prv tx scn: 0x0000.c01937c3
txn start scn: scn: 0x0000.c0193cbe logon user: 84
prev brb: 12603230 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0100059f hdba: 0x0100059a
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 1(0x1) size/delt: 7
fb: --H-FL-- lb: 0x0 cc: 2
null: --
col 0: [ 1] 32
col 1: [ 1] 62
--看看Rec=0x03,objn: 270081(0x00041f01) objd: 270081就是对应的对象.tblspc: 4(0x00000004)对应的就是文件编号.
--bdba: 0x0100059f ,里面就包含了块号. slot: 4(0x1)对应的就是行号1.
--hdba: 0x0100059a,表示该对象的块头.
--另外由于该事务仅仅删除1行记录.rci=0x00,表示前面的结束.
SQL> @dfb 0100059f
RFILE# BLOCK#
---------- ----------
4 1439
SQL> @dfb 0100059a
RFILE# BLOCK#
---------- ----------
4 1434
SQL> select object_name, OBJECT_ID ,DATA_OBJECT_ID from dba_objects where object_name='T' and wner=user;
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
-------------------- --------- --------------
T 270081 270081
SQL> select header_file,header_block from dba_segments where wner=user and segment_name='T';
HEADER_FILE HEADER_BLOCK
----------- ------------
4 1434
--由于执行的是删除操作,需要在UNDO中记录被删除行所有列的值(前镜像)及ROWID。
col 0: [ 1] 32
col 1: [ 1] 62
SQL> select dump('2',16),dump('b',16) from dual ;
DUMP('2',16) DUMP('B',16)
---------------- ----------------
Typ=96 Len=1: 32 Typ=96 Len=1: 62
--这样正好对上!所以讲如果delete要回滚的话,代价是很大的.
时间: 2024-09-22 11:38:46