Redo內部解析-Single Row Delete （六）

九、刪除一行資料

    1、建立實驗環境

     SQL>col scn format 999999999990
     SQL>select dbms_flashback.get_system_change_number scn from dual;
     SQL>delete from test where id=1;
     SQL>select dbms_flashback.get_system_change_number scn from dual;

     SQL>alter system dump logfile
                '/home/xxxx/oracle/oradata/xxxxx/redo02.log'
              scn min 56402955922
              scn max 56402955923;

    2、解析trace檔案

REDO RECORD - Thread:1 RBA: 0x005ffc.00000545.0010 LEN: 0x01d4 VLD: 0x0d
SCN: 0x000d.21e0ce93 SUBSCN:  6 07/21/2009 16:21:13
CHANGE #1 TYP:2 CLS: 1 AFN:4 DBA:0x01000010 OBJ:142708 SCN:0x000d.21e0caec SEQ:  2 OP:11.3
KTB Redo
op: 0x11  ver: 0x01
op: F  xid:  0x0007.02a.00005303    uba: 0x008009e0.4ada.1a
Block cleanout record, scn:  0x000d.21e0ce93 ver: 0x01 opt: 0x02, entries follow...
  itli: 1  flg: 2  scn: 0x000d.21e0caec
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01000010  hdba: 0x0100000b
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0)
--OP: 11.3 Delete單行資料
--DRP: delete row piece
--Type:2 Delayed logging ,CLS:1 Data Block
--DBA:01000010 可以轉化成file#, block#，具體參看"Redo內部解析（二）"
--OBJ:對應dba_objects.object_id
--Xid: usn#.slot#.wrap#
--UBA: undo block addr.Seq#.Record#

CHANGE #2 TYP:0 CLS:29 AFN:2 DBA:0x00800069 OBJ:4294967295 SCN:0x000d.21e0ce19 SEQ:  1 OP:5.2
ktudh redo: slt: 0x002a sqn: 0x00005303 flg: 0x0012 siz: 176 fbi: 0
            uba: 0x008009e0.4ada.1a    pxid:  0x0000.000.00000000
--update undo header,具體段位置(29-11)/2=9
--type 0: normal

CHANGE #3 TYP:0 CLS:30 AFN:2 DBA:0x008009e0 OBJ:4294967295 SCN:0x000d.21e0ce18 SEQ:  1 OP:5.1
ktudb redo: siz: 176 spc: 2568 flg: 0x0012 seq: 0x4ada rec: 0x1a
            xid:  0x0007.02a.00005303
ktubl redo: slt: 42 rci: 0 opc: 11.1 objn: 142708 objd: 142708 tsn: 4
Undo type:  Regular undo        Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
             0x00000000  prev ctl uba: 0x008009e0.4ada.19
prev ctl max cmt scn:  0x000d.21e0c5eb  prev tx cmt scn:  0x000d.21e0c71d
txn start scn:  0x000d.21e0caec  logon user: 55  prev brb: 8391107  prev bcl: 0 KDO undo record:
KTB Redo
op: 0x04  ver: 0x01
op: L  itl: xid:  0x0007.02b.00005308 uba: 0x008009d6.4ada.13
                      flg: C---    lkc:  0     scn: 0x000d.21e0c98a
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01000010  hdba: 0x0100000b
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) size/delt: 11
fb: --H-FL-- lb: 0x0  cc: 2
null: --
col  0: [ 2]  c1 02
col  1: [ 4]  74 65 73 74

--Undo記錄操作相反的語句，IRP：Insert Row Piece
--插入資料值記錄col 0和col 1

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/354732/viewspace-609811/，如需轉載，請註明出處，否則將追究法律責任。