undo 练习学习笔记。

   今天是2013-08-24，我把自己学习的内容贴一下，做个笔记。

SQL> select obj#,name from t where rownum<5;

      OBJ# NAME
---------- --------------------------------------------------
        20 RHYS
        46 I_USER1
        28 CON$
        15 UNDO$

SQL> update t set obj#=22 where name='RHYS';

1 row updated.

SQL> SELECT OBJ#,NAME FROM T WHERE NAME='RHYS';

      OBJ# NAME
---------- --------------------------------------------------
        22 RHYS

SQL> select usn,rssize,xacts,status from v$rollstat where xacts>0;               

       USN     RSSIZE      XACTS STATUS
---------- ---------- ---------- ---------------
        14    2088960          1 ONLINE

SQL> select * from v$rollname;

       USN NAME
---------- --------------------------------------------------
         0 SYSTEM
        11 _SYSSMU11_3454391606$
        12 _SYSSMU12_2645405069$
        13 _SYSSMU13_2148091021$
        14 _SYSSMU14_3755651093$
        15 _SYSSMU15_3449542437$
        16 _SYSSMU16_2643722535$
        17 _SYSSMU17_2278473149$
        18 _SYSSMU18_2537237058$
        19 _SYSSMU19_3518593211$
        20 _SYSSMU20_912969169$

11 rows selected.

SQL> alter system dump undo header '_SYSSMU14_3755651093$';  

System altered.

SQL> select xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn from v$transaction where xidusn=14;

    XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN
---------- ---------- ---------- ---------- ---------- ----------
        14         21        207          8      20188       1022

SQL> col value for a60;
SQL> select * from v$diag_info where name='Default Trace File';

   INST_ID NAME                                               VALUE
---------- -------------------------------------------------- ------------------------------------------------------------
         1 Default Trace File                                 /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_ora_2200.trc

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle-one ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Aug 24 17:38:51 2013

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system dump datafile 8 block 20188;

System altered.

SQL> col value for a60
SQL> col name for a40
SQL> select * from v$diag_info where name='Default Trace File';

   INST_ID NAME
---------- ----------------------------------------
VALUE
------------------------------------------------------------
         1 Default Trace File
/opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_ora_2686.trc

在事务表 中看到：
 TRN TBL::

  index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt
  ------------------------------------------------------------------------------------------------
   0x15   10    0x80  0x00cf  0x000f  0x0000.00a840ae  0x02004edc  0x0000.000.00000000  0x00000001   0x00000000  0
找到数据对应的前镜像条目：
10000000000100111011011100----》datafile 8 block 20188
找到数据的前镜像：
UNDO BLK:
xid: 0x000e.015.000000cf  seq: 0x3fe cnt: 0xf   irb: 0xf   icl: 0x0   flg: 0x0000

 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f38     0x02 0x1ecc     0x03 0x1e60     0x04 0x1db0     0x05 0x1d28
0x06 0x1c9c     0x07 0x1c34     0x08 0x1b74     0x09 0x1ab4     0x0a 0x1a10
0x0b 0x19a4     0x0c 0x191c     0x0d 0x18a0     0x0e 0x1838     0x0f 0x1794
*-----------------------------
* Rec #0xf  slt: 0x15  objn: 73900(0x000120ac)  objd: 73900  tblspc: 0(0x00000000)
*       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: 0x02004edc.03fe.0c ctl max scn: 0x0000.00a83ce5 prv tx scn: 0x0000.00a83cf2
txn start scn: scn: 0x0000.00000000 logon user: 0
 prev brb: 33574613 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 18 nnew: 1 size: 0
KDO Op code:  21 row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x00414909  hdba: 0x00414908
itli: 3  ispac: 0  maxfr: 4863
vect = 0
col  0: [ 2]  c1 15
SQL> select utl_raw.cast_to_number(replace ('c1 15',' ')) num from dual;

       NUM
----------
        20

SQL>
找到了update 的前镜像。
2）insert产生什么undo呢？

SQL> select * from t1;

        ID        SAL JOB
---------- ---------- ----------
         1          2 a
         2          3 b
         3          4 c
         1          3 b
         2          1 a
                      D

6 rows selected.

SQL> insert into t1 values(1,9,'z');

1 row created.

SQL> select usn,rssize,xacts,status,wraps from v$rollstat where xacts>0;

       USN     RSSIZE      XACTS STATUS               WRAPS
---------- ---------- ---------- --------------- ----------
        19    8708096          1 ONLINE                   5

SQL> select * from v$rollname;

       USN NAME
---------- ----------------------------------------
         0 SYSTEM
        11 _SYSSMU11_3454391606$
        12 _SYSSMU12_2645405069$
        13 _SYSSMU13_2148091021$
        14 _SYSSMU14_3755651093$
        15 _SYSSMU15_3449542437$
        16 _SYSSMU16_2643722535$
        17 _SYSSMU17_2278473149$
        18 _SYSSMU18_2537237058$
        19 _SYSSMU19_3518593211$
        20 _SYSSMU20_912969169$

11 rows selected.

SQL> alter system dump undo header '_SYSSMU19_3518593211$';

System altered.

SQL> col value for a60
SQL> select * from v$diag_info where name='Default Trace File';

   INST_ID NAME                                     VALUE
---------- ---------------------------------------- ------------------------------------------------------------
         1 Default Trace File                       /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_ora_2871.trc
查看事务表：
  TRN TBL::

获得数据 块
UNDO BLK:
xid: 0x0012.015.000000d0  seq: 0x3de cnt: 0x3   irb: 0x3   icl: 0x0   flg: 0x0000

 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f60     0x02 0x1ed4     0x03 0x1e6c
* Rec #0x3  slt: 0x15  objn: 68397(0x00010b2d)  objd: 68397  tblspc: 1(0x00000001)
*       Layer:  10 (Index)   opc: 22   rci 0x02   
Undo type:  Regular undo   Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*-----------------------------
index undo for leaf key operations
KTB Redo
op: 0x04  ver: 0x01 
compat bit: 4 (post-11) padding: 1
op: L  itl: xid:  0x000f.012.000000d3 uba: 0x02004d60.03e1.30
                      flg: C---    lkc:  0     scn: 0x0000.00a847de
Dump kdilk : itl=2, kdxlkflg=0x1 sdc=0 indexid=0x808142 block=0x00808146
(kdxlpu): purge leaf row
key :(15):  07 78 71 08 18 13 09 3b 06 00 80 81 33 00 44
3）delete record记录
SQL> select * from t1;

        ID        SAL JOB
---------- ---------- ----------
         1          9 z
         7          7 b
         1          2 a
         2          3 b
         3          4 c
         1          3 b
         2          1 a
                      D

8 rows selected.

SQL> delete from t1 where sal=7;

1 row deleted.

SQL>
SQL> select usn  from v$rollstat where xacts>0;                   

       USN
----------
        20

SQL> select xidusn,xidslot,ubafil,ubablk from v$transaction where xidusn=20;

    XIDUSN    XIDSLOT     UBAFIL     UBABLK
---------- ---------- ---------- ----------
        20         13          8        277

SQL> alter system dump datafile 8  block 277;

System altered.

SQL> col value fro a50
SP2-0158: unknown COLUMN option "fro"
SQL> col  value for a50
SQL> select * from v$diag_info where name='Default Trace File';

   INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
--------------------------------------------------
         1 Default Trace File
/opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_or
a_3225.trc

SQL>
查看数据记录：
*-----------------------------
* Rec #0x11  slt: 0x0c  objn: 287(0x0000011f)  objd: 287  tblspc: 0(0x00000000)
*       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: 0x02000115.03b8.0e ctl max scn: 0x0000.00a849b8 prv tx scn: 0x0000.00a849c4
txn start scn: scn: 0x0000.00a84cf2 logon user: 0
 prev brb: 33554706 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04  ver: 0x01 
compat bit: 4 (post-11) padding: 1
op: L  itl: xid:  0x0011.00b.000000bb uba: 0x020000ef.0247.06
                      flg: C---    lkc:  0     scn: 0x0000.00a84cda
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x004007d9  hdba: 0x004007d8
itli: 1  ispac: 0  maxfr: 4863
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 26
ncol: 19 nnew: 6 size: 7
col  4: [ 7]  78 71 08 18 13 25 02
col  5: [ 7]  78 71 08 18 13 26 03
col  6: [ 7]  78 71 08 18 13 26 02
col  7: [21]
 c0 02 47 26 04 47 26 04 47 26 04 47 26 04 47 26 04 47 26 05 18
col  9: [ 1]  80
col 10: [ 1]  80
 
*-----------------------------
* Rec #0x12  slt: 0x0c  objn: 289(0x00000121)  objd: 289  tblspc: 0(0x00000000)
*       Layer:  10 (Index)   opc: 22   rci 0x11   
Undo type:  Regular undo   Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
ablespace Undo:  No
rdba: 0x00000000
*-----------------------------
index undo for leaf key operations
KTB Redo
op: 0x04  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L  itl: xid:  0x0010.010.000000d2 uba: 0x02004d4f.0264.06
                      flg: C---    lkc:  0     scn: 0x0000.00a84c93
Dump kdilk : itl=3, kdxlkflg=0x1 sdc=0 indexid=0x4007e8 block=0x004007e9
(kdxlre): restore leaf row (clear leaf delete flags)
key :(15):  07 78 71 08 18 13 26 02 06 00 40 07 d9 00 02

*-----------------------------
* Rec #0x13  slt: 0x0c  objn: 289(0x00000121)  objd: 289  tblspc: 0(0x00000000)
*       Layer:  10 (Index)   opc: 22   rci 0x12
Undo type:  Regular undo   Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*-----------------------------
index undo for leaf key operations
KTB Redo
op: 0x02  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C  uba: 0x02000115.03b8.12
Dump kdilk : itl=3, kdxlkflg=0x1 sdc=0 indexid=0x4007e8 block=0x004007e9
(kdxlpu): purge leaf row
key :(15):  07 78 71 08 18 13 27 03 06 00 40 07 d9 00 02