事物的基本流程

事物的基本流程(摘自《深入解析oracle》)：

1  首先當一個事物開始時，需要在一個回滾段事物表上分配一個事物槽。

2  在資料庫頭部獲取一個ITL事物槽，改事物槽指向回滾段頭的事物槽

3 在修改資料之前，需要記錄前映象資訊，這個資訊以undo record的形式儲存在回滾段中，回滾段段頭事物槽指向該記錄

4  鎖定修改行，修改行鎖定位（lb），指向ITL事物槽

5 資料修改可以進行

 

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL> update emp set sal=4000 where empno=7788;

1 row updated.

SQL> update emp set sal=4000 where empno=7782;

1 row updated.

SQL> update emp set sal=4000 where empno=7698;

1 row updated.

 

 

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Apr 24 21:14:45 2011

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select  xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction;

    XIDUSN    XIDSLOT     XIDSQN     UBABLK     UBAFIL     UBAREC
---------- ---------- ---------- ---------- ---------- ----------
         4         42        268        354          2         37

SQL> select usn from v$rollstat;

       USN
----------
         0
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

11 rows selected.

SQL> select * from v$rollname where usn=4;

       USN NAME
---------- ------------------------------
         4 _SYSSMU4$

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

System altered.

SQL> SELECT    a.VALUE
  2         || b.symbol
  3         || c.instance_name
  4         || '_ora_'
  5         || d.spid
  6         || '.trc' trace_file
  7    FROM (SELECT VALUE
  8            FROM v$parameter
  9           WHERE NAME = 'user_dump_dest') a,
 10         (SELECT SUBSTR (VALUE, -6, 1) symbol
 11            FROM v$parameter
 12           WHERE NAME = 'user_dump_dest') b,
 13         (SELECT instance_name
 14            FROM v$instance) c,
 15         (SELECT spid
 16            FROM v$session s, v$process p, v$mystat m
 17           WHERE s.paddr = p.addr AND s.SID = m.SID AND m.statistic# = 0) d;

TRACE_FILE
--------------------------------------------------------------------------------
/data/ora10g/admin/RACDBSTD/udump/RACDBSTD_ora_9428.trc

SQL> alter system dump datafile 2 block 354;

System altered.

SQL> select dbms_utility.data_block_address_file(4246642) "file",dbms_utility.data_block_address_block(4246642) "block"
from dual;  2 

      file      block
---------- ----------
         1      52338

SQL> alter system dump datafile 1 block 52338;

System altered.

 

 

 

 

 

********************************************************************************
UNDO BLK:
xid: 0x0004.02a.0000010c  seq: 0xa7  cnt: 0x27  irb: 0x27  icl: 0x0   flg: 0x0000

 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f84     0x02 0x1f2c     0x03 0x1ed4     0x04 0x1e04     0x05 0x1da4
0x06 0x1d4c     0x07 0x1cf4     0x08 0x1c1c     0x09 0x1bb8     0x0a 0x1b60
0x0b 0x1b08     0x0c 0x1a34     0x0d 0x19d4     0x0e 0x197c     0x0f 0x1924
0x10 0x1854     0x11 0x17f8     0x12 0x17a0     0x13 0x1748     0x14 0x1674
0x15 0x1614     0x16 0x15bc     0x17 0x1564     0x18 0x1490     0x19 0x1430
0x1a 0x13d8     0x1b 0x1380     0x1c 0x12ac     0x1d 0x124c     0x1e 0x11f4
0x1f 0x119c     0x20 0x107c     0x21 0x0f9c     0x22 0x0ec0     0x23 0x0e58
0x24 0x0de8     0x25 0x0d68     0x26 0x0d0c     0x27 0x0cb0

 

*-----------------------------
* Rec #0x25  slt: 0x2a  objn: 46095(0x0000b40f)  objd: 46095  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: 0x00000000
*-----------------------------
uba: 0x00800161.00a7.25 ctl max scn: 0x0000.0010143f prv tx scn: 0x0000.0010152e
txn start scn: scn: 0x0000.00000000 logon user: 0
 prev brb: 8388909 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03  ver: 0x01
op: Z
KDO Op code: URP row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x0040cc72  hdba: 0x0040cc71
itli: 2  ispac: 0  maxfr: 4863
tabn: 0 slot: 7(0x7) flag: 0x2c lock: 0 ckix: 41
ncol: 8 nnew: 1 size: 0
Vector content:
col  5: [ 2]  c2 1f

*-----------------------------
* Rec #0x26  slt: 0x2a  objn: 46095(0x0000b40f)  objd: 46095  tblspc: 0(0x00000000)
*       Layer:  11 (Row)   opc: 1   rci 0x25
Undo type:  Regular undo   Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x00800162.00a7.25
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0040cc72  hdba: 0x0040cc71
itli: 2  ispac: 0  maxfr: 4863
tabn: 0 slot: 6(0x6) flag: 0x2c lock: 0 ckix: 41
ncol: 8 nnew: 1 size: 1
col  5: [ 3]  c2 19 33

*-----------------------------
* Rec #0x27  slt: 0x2a  objn: 46095(0x0000b40f)  objd: 46095  tblspc: 0(0x00000000)
*       Layer:  11 (Row)   opc: 1   rci 0x26
Undo type:  Regular undo   Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x00800162.00a7.26
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0040cc72  hdba: 0x0040cc71
itli: 2  ispac: 0  maxfr: 4863
tabn: 0 slot: 5(0x5) flag: 0x2c lock: 0 ckix: 191
ncol: 8 nnew: 1 size: 1
col  5: [ 3]  c2 1d 33

 

 

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0008.02b.0000010c  0x00800216.005c.02  C---    0  scn 0x0000.0010205e
0x02   0x0004.02a.0000010c  0x00800162.00a7.27  ----    3  fsc 0x0002.00000000

data_block_dump,data header at 0xeb8845c
===============
tsiz: 0x1fa0
hsiz: 0x2e
pbl: 0x0eb8845c
bdba: 0x0040cc72
     76543210
flag=--------
ntab=1
nrow=14
frre=-1
fsbo=0x2e
fseo=0x1d19
avsp=0x1d3b
tosp=0x1d3d
0xe:pti[0]      nrow=14 ffs=0
0x12:pri[0]     ffs=0x1f7a
0x14:pri[1]     ffs=0x1f4f
0x16:pri[2]     ffs=0x1f24
0x18:pri[3]     ffs=0x1efb
0x1a:pri[4]     ffs=0x1ece
0x1c:pri[5]     ffs=0x1d19
0x1e:pri[6]     ffs=0x1d41
0x20:pri[7]     ffs=0x1e54
0x22:pri[8]     ffs=0x1e2e
0x24:pri[9]     ffs=0x1e03
0x26:pri[10]    ffs=0x1ddd
0x28:pri[11]    ffs=0x1db7
0x2a:pri[12]    ffs=0x1d90
0x2c:pri[13]    ffs=0x1d69
block_row_dump:
tab 0, row 0, @0x1f7a
tl: 38 fb: --H-FL-- lb: 0x0  cc: 8
col  0: [ 3]  c2 4a 46
col  1: [ 5]  53 4d 49 54 48
col  2: [ 5]  43 4c 45 52 4b
col  3: [ 3]  c2 50 03
col  4: [ 7]  77 b4 0c 11 01 01 01
col  5: [ 2]  c2 09
col  6: *NULL*
col  7: [ 2]  c1 15
tab 0, row 1, @0x1f4f
tl: 43 fb: --H-FL-- lb: 0x0  cc: 8
col  0: [ 3]  c2 4b 64
col  1: [ 5]  41 4c 4c 45 4e
col  2: [ 8]  53 41 4c 45 53 4d 41 4e
col  3: [ 3]  c2 4d 63
col  4: [ 7]  77 b5 02 14 01 01 01
col  5: [ 2]  c2 11
col  6: [ 2]  c2 04
col  7: [ 2]  c1 1f
tab 0, row 2, @0x1f24
tl: 43 fb: --H-FL-- lb: 0x0  cc: 8
col  0: [ 3]  c2 4c 16
col  1: [ 4]  57 41 52 44
col  2: [ 8]  53 41 4c 45 53 4d 41 4e
col  3: [ 3]  c2 4d 63
col  4: [ 7]  77 b5 02 16 01 01 01
col  5: [ 3]  c2 0d 33
col  6: [ 2]  c2 06
col  7: [ 2]  c1 1f
tab 0, row 3, @0x1efb
tl: 41 fb: --H-FL-- lb: 0x0  cc: 8
col  0: [ 3]  c2 4c 43
col  1: [ 5]  4a 4f 4e 45 53
col  2: [ 7]  4d 41 4e 41 47 45 52
col  3: [ 3]  c2 4f 28
col  4: [ 7]  77 b5 04 02 01 01 01
col  5: [ 3]  c2 1e 4c
col  6: *NULL*
col  7: [ 2]  c1 15
tab 0, row 4, @0x1ece
tl: 45 fb: --H-FL-- lb: 0x0  cc: 8
col  0: [ 3]  c2 4d 37
col  1: [ 6]  4d 41 52 54 49 4e
col  2: [ 8]  53 41 4c 45 53 4d 41 4e
col  3: [ 3]  c2 4d 63
col  4: [ 7]  77 b5 09 1c 01 01 01
col  5: [ 3]  c2 0d 33
col  6: [ 2]  c2 0f
col  7: [ 2]  c1 1f
tab 0, row 5, @0x1d19
tl: 40 fb: --H-FL-- lb: 0x2  cc: 8
col  0: [ 3]  c2 4d 63
col  1: [ 5]  42 4c 41 4b 45
col  2: [ 7]  4d 41 4e 41 47 45 52
col  3: [ 3]  c2 4f 28
col  4: [ 7]  77 b5 05 01 01 01 01
col  5: [ 2]  c2 29
col  6: *NULL*
col  7: [ 2]  c1 1f
tab 0, row 6, @0x1d41
tl: 40 fb: --H-FL-- lb: 0x2  cc: 8
col  0: [ 3]  c2 4e 53
col  1: [ 5]  43 4c 41 52 4b
col  2: [ 7]  4d 41 4e 41 47 45 52
col  3: [ 3]  c2 4f 28
col  4: [ 7]  77 b5 06 09 01 01 01
col  5: [ 2]  c2 29
col  6: *NULL*
col  7: [ 2]  c1 0b
tab 0, row 7, @0x1e54
tl: 40 fb: --H-FL-- lb: 0x2  cc: 8
col  0: [ 3]  c2 4e 59
col  1: [ 5]  53 43 4f 54 54
col  2: [ 7]  41 4e 41 4c 59 53 54
col  3: [ 3]  c2 4c 43
col  4: [ 7]  77 bb 04 13 01 01 01
col  5: [ 2]  c2 29
col  6: *NULL*
col  7: [ 2]  c1 15
tab 0, row 8, @0x1e2e
tl: 38 fb: --H-FL-- lb: 0x0  cc: 8
col  0: [ 3]  c2 4f 28
col  1: [ 4]  4b 49 4e 47
col  2: [ 9]  50 52 45 53 49 44 45 4e 54
col  3: *NULL*
col  4: [ 7]  77 b5 0b 11 01 01 01
col  5: [ 2]  c2 33
col  6: *NULL*
col  7: [ 2]  c1 0b
tab 0, row 9, @0x1e03
tl: 43 fb: --H-FL-- lb: 0x0  cc: 8
col  0: [ 3]  c2 4f 2d
col  1: [ 6]  54 55 52 4e 45 52
col  2: [ 8]  53 41 4c 45 53 4d 41 4e
col  3: [ 3]  c2 4d 63
col  4: [ 7]  77 b5 09 08 01 01 01
col  5: [ 2]  c2 10
col  6: [ 1]  80
col  7: [ 2]  c1 1f
tab 0, row 10, @0x1ddd
tl: 38 fb: --H-FL-- lb: 0x0  cc: 8
col  0: [ 3]  c2 4f 4d
col  1: [ 5]  41 44 41 4d 53
col  2: [ 5]  43 4c 45 52 4b
col  3: [ 3]  c2 4e 59
col  4: [ 7]  77 bb 05 17 01 01 01
col  5: [ 2]  c2 0c
col  6: *NULL*
col  7: [ 2]  c1 15
tab 0, row 11, @0x1db7
tl: 38 fb: --H-FL-- lb: 0x0  cc: 8
col  0: [ 2]  c2 50
col  1: [ 5]  4a 41 4d 45 53
col  2: [ 5]  43 4c 45 52 4b
col  3: [ 3]  c2 4d 63
col  4: [ 7]  77 b5 0c 03 01 01 01
col  5: [ 3]  c2 0a 33
col  6: *NULL*
col  7: [ 2]  c1 1f
tab 0, row 12, @0x1d90
tl: 39 fb: --H-FL-- lb: 0x0  cc: 8
col  0: [ 3]  c2 50 03
col  1: [ 4]  46 4f 52 44
col  2: [ 7]  41 4e 41 4c 59 53 54
col  3: [ 3]  c2 4c 43
col  4: [ 7]  77 b5 0c 03 01 01 01
col  5: [ 2]  c2 1f
col  6: *NULL*
col  7: [ 2]  c1 15
tab 0, row 13, @0x1d69
tl: 39 fb: --H-FL-- lb: 0x0  cc: 8
col  0: [ 3]  c2 50 23
col  1: [ 6]  4d 49 4c 4c 45 52
col  2: [ 5]  43 4c 45 52 4b
col  3: [ 3]  c2 4e 53
col  4: [ 7]  77 b6 01 17 01 01 01
col  5: [ 2]  c2 0e
col  6: *NULL*
col  7: [ 2]  c1 0b
end_of_block_dump
End dump data blocks tsn: 0 file#: 1 minblk 52338 maxblk 52338