Huaiyao's Notes

Transaction and undo

Run a transaction.

1
2
3
4
5
6
7
8
SQL> create table rjin as select * from dba_objects ;
Table created.
SQL> update rjin set OBJECT_ID=OBJECT_ID+1;
74917 rows updated.

Check v$transaction.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
SQL> select * from v$transaction
2 @pr
Pivoting output....
==============================
ADDR : 000000048A7EBBD0
XIDUSN : 7
XIDSLOT : 17
XIDSQN : 1979
UBAFIL : 3
UBABLK : 2139
UBASQN : 359
UBAREC : 3
STATUS : ACTIVE
START_TIME : 06/26/17 06:21:41
START_SCNB : 1721701
START_SCNW : 0
START_UEXT : 12
START_UBAFIL : 3
START_UBABLK : 874
START_UBASQN : 347
START_UBAREC : 39
SES_ADDR : 00000004A0774750
FLAG : 3587
SPACE : NO
RECURSIVE : NO
NOUNDO : NO
PTX : NO
NAME :
PRV_XIDUSN : 0
PRV_XIDSLT : 0
PRV_XIDSQN : 0
PTX_XIDUSN : 0
PTX_XIDSLT : 0
PTX_XIDSQN : 0
DSCN-B : 0
DSCN-W : 0
USED_UBLK : 1120
USED_UREC : 81019
LOG_IO : 257079
PHY_IO : 816
CR_GET : 2541
CR_CHANGE : 0
START_DATE : 26-JUN-17
DSCN_BASE : 0
DSCN_WRAP : 0
START_SCN : 1721701
DEPENDENT_SCN : 0
XID : 00070011000007BB
PRV_XID : 0000000000000000
PTX_XID : 0000000000000000
PL/SQL procedure successfully completed.

Check dba_rollback_segs.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL> select * from dba_rollback_segs where segment_id=7
2 @pr
Pivoting output....
==============================
SEGMENT_NAME : _SYSSMU7_3227937682$
OWNER : PUBLIC
TABLESPACE_NAME : UNDOTBS1
SEGMENT_ID : 7
FILE_ID : 3
BLOCK_ID : 224
INITIAL_EXTENT : 131072
NEXT_EXTENT : 65536
MIN_EXTENTS : 2
MAX_EXTENTS : 32765
PCT_INCREASE :
STATUS : ONLINE
INSTANCE_NUM :
RELATIVE_FNO : 3
PL/SQL procedure successfully completed.

Check x$ktuxe.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
SQL> select * from x$ktuxe where ktuxeusn=7;
ADDR INDX INST_ID KTUXEUSN KTUXESLT KTUXESQN KTUXERDBF KTUXERDBB KTUXESCNB KTUXESCNW KTUXESTA KTUXECFL KTUXEUEL KTUXEDDBF KTUXEDDBB KTUXEPUSN KTUXEPSLT KTUXEPSQN KTUXESIZ
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ----------
FFFFFFFF7977A858 0 1 7 0 1980 0 0 1721664 0 INACTIVE NONE 18 0 0 0 0 0 0
FFFFFFFF7977A8B0 1 1 7 1 1979 3 860 1721634 0 INACTIVE NONE 25 0 0 0 0 0 1
FFFFFFFF7977A908 2 1 7 2 1979 3 860 1721640 0 INACTIVE NONE 20 0 0 0 0 0 1
FFFFFFFF7977A960 3 1 7 3 1983 3 871 1721684 0 INACTIVE NONE 5 0 0 0 0 0 1
FFFFFFFF7977A9B8 4 1 7 4 1980 3 858 1721701 0 INACTIVE NONE -1 0 0 0 0 0 1
FFFFFFFF7977AA10 5 1 7 5 1981 0 0 1721686 0 INACTIVE NONE 23 0 0 0 0 0 0
FFFFFFFF7977AA68 6 1 7 6 1984 3 874 1721699 0 INACTIVE NONE 4 0 0 0 0 0 1
FFFFFFFF7977AAC0 7 1 7 7 1982 3 874 1721696 0 INACTIVE NONE 16 0 0 0 0 0 1
FFFFFFFF7977AB18 8 1 7 8 1979 0 0 1721656 0 INACTIVE NONE 11 0 0 0 0 0 0
FFFFFFFF7977AB70 9 1 7 9 1982 3 868 1721672 0 INACTIVE NONE 30 0 0 0 0 0 1
FFFFFFFF7977ABC8 10 1 7 10 1983 0 0 1721648 0 INACTIVE NONE 28 0 0 0 0 0 0
FFFFFFFF7977AC20 11 1 7 11 1976 0 0 1721658 0 INACTIVE NONE 26 0 0 0 0 0 0
FFFFFFFF7977AC78 12 1 7 12 1983 3 874 1721694 0 INACTIVE NONE 7 0 0 0 0 0 1
FFFFFFFF7977ACD0 13 1 7 13 1981 0 0 1721668 0 INACTIVE NONE 9 0 0 0 0 0 0
FFFFFFFF7977AD28 14 1 7 14 1981 0 0 1721678 0 INACTIVE NONE 21 0 0 0 0 0 0
FFFFFFFF7977AD80 15 1 7 15 1981 0 0 1721646 0 INACTIVE NONE 10 0 0 0 0 0 0
FFFFFFFF7977ADD8 16 1 7 16 1979 3 874 1721697 0 INACTIVE NONE 6 0 0 0 0 0 1
FFFFFFFF7977AE30 17 1 7 17 1979 3 2139 1721701 0 ACTIVE NONE 12 0 0 0 0 0 1120
FFFFFFFF7977AE88 18 1 7 18 1983 0 0 1721666 0 INACTIVE NONE 13 0 0 0 0 0 0
FFFFFFFF7977AEE0 19 1 7 19 1980 3 860 1721630 0 INACTIVE NONE 31 0 0 0 0 0 1
FFFFFFFF7977AF38 20 1 7 20 1982 3 860 1721642 0 INACTIVE NONE 32 0 0 0 0 0 1
FFFFFFFF7977AF90 21 1 7 21 1981 3 871 1721682 0 INACTIVE NONE 3 0 0 0 0 0 1
FFFFFFFF7977AFE8 22 1 7 22 1981 3 860 1721638 0 INACTIVE NONE 2 0 0 0 0 0 1
FFFFFFFF7977B040 23 1 7 23 1981 0 0 1721688 0 INACTIVE NONE 29 0 0 0 0 0 0
FFFFFFFF7977B098 24 1 7 24 1981 3 860 1721628 0 INACTIVE NONE 19 0 0 0 0 0 1
FFFFFFFF7977B0F0 25 1 7 25 1980 3 860 1721636 0 INACTIVE NONE 22 0 0 0 0 0 1
FFFFFFFF7977B148 26 1 7 26 1982 3 865 1721662 0 INACTIVE NONE 0 0 0 0 0 0 1
FFFFFFFF7977B1A0 27 1 7 27 1980 0 0 1721654 0 INACTIVE NONE 8 0 0 0 0 0 0
FFFFFFFF7977B1F8 28 1 7 28 1982 3 862 1721652 0 INACTIVE NONE 27 0 0 0 0 0 1
FFFFFFFF7977B250 29 1 7 29 1980 0 0 1721690 0 INACTIVE NONE 12 0 0 0 0 0 0
FFFFFFFF7977B2A8 30 1 7 30 1973 0 0 1721674 0 INACTIVE NONE 33 0 0 0 0 0 0
FFFFFFFF7977B300 31 1 7 31 1976 3 860 1721632 0 INACTIVE NONE 1 0 0 0 0 0 1
FFFFFFFF7977B358 32 1 7 32 1978 0 0 1721644 0 INACTIVE NONE 15 0 0 0 0 0 0
FFFFFFFF7977B3B0 33 1 7 33 1982 0 0 1721676 0 INACTIVE NONE 14 0 0 0 0 0 0
34 rows selected.

There are 34 transacton slots in one undo segement in 11g.

Check x$ktuxe for active transaction.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SQL> select * from x$ktuxe where ktuxeusn=7 and KTUXESTA='ACTIVE'
2 @pr
Pivoting output....
==============================
ADDR : FFFFFFFF788F85D0
INDX : 17
INST_ID : 1
KTUXEUSN : 7
KTUXESLT : 17
KTUXESQN : 1979
KTUXERDBF : 3
KTUXERDBB : 2139
KTUXESCNB : 1721701
KTUXESCNW : 0
KTUXESTA : ACTIVE
KTUXECFL : NONE
KTUXEUEL : 12
KTUXEDDBF : 0
KTUXEDDBB : 0
KTUXEPUSN : 0
KTUXEPSLT : 0
KTUXEPSQN : 0
KTUXESIZ : 1120
PL/SQL procedure successfully completed.

Some relation.

  • v$transaction

    1
    2
    3
    4
    5
    6
    XIDUSN : 7 --> Undo segment number
    XIDSLOT : 17 --> Undo slot
    XIDSQN : 1979 --> Undo sequence
    UBAFIL : 3 --> Undo file number
    UBABLK : 2139 --> Undo block number
    USED_UBLK : 1120 --> Used undo block count
  • dba_rollback_segs

    1
    2
    SEGMENT_ID : 7 --> Undo segment number
    FILE_ID : 3 --> Undo file ID number
  • x$ktuxe

    1
    2
    3
    4
    5
    6
    KTUXEUSN : 7 --> Undo segment number
    KTUXESLT : 17 --> Undo slot
    KTUXESQN : 1979 --> Undo sequence
    KTUXERDBF : 3 --> Undo file number
    KTUXERDBB : 2139 --> Undo block number
    KTUXESIZ : 1120 --> Used undo block count