<pre name="code" class="sql">v$transaction: 列出了没有提交的活动事务
2. XID的结构
eg
SQL> ---session1
SQL> drop table test_undo1 purge;
Table dropped.
SQL> create table test_undo1 as select * from dba_objects;
Table created.
SQL> set linesize 200
SQL> select rowid,
dbms_rowid.rowid_object(rowid) object_id,
dbms_rowid.rowid_relative_fno(rowid) file_id,
dbms_rowid.rowid_block_number(rowid) block_id,
dbms_rowid.rowid_row_number(rowid) num,
rowidtochar(rowid)
from test_undo1
where object_id = 1051; 2 3 4 5 6 7 8
ROWID OBJECT_ID FILE_ID BLOCK_ID NUM ROWIDTOCHAR(ROWID)
------------------ ---------- ---------- ---------- ---------- ------------------
AAApgPAALAAACkWAAC 169999 11 10518 2 AAApgPAALAAACkWAAC
SQL> select sid from v$mystat where rownum<2;
SID
----------
1625
SQL> delete from test_undo1 where object_id=1051;
1 row deleted.
SQL> SQL>
产生一个事务:
SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec,to_char(START_SCN,'999999999999') from v$transaction;
XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL UBAREC TO_CHAR(START
---------- ---------- ---------- ---------- ---------- ---------- -------------
1 17 173289 303 2 29 57693468601
其中XINUSN 表示回滚段号Undo Segement number
XIDSLOT 事务槽号
XIDSQN 事务编号
UBABLK UBA 块号
-----dump 数据块
SQL> alter system dump datafile 11 block 10518;
System altered.
Block header dump: 0x02c02916
Object id on Block? Y
seg/obj: 0x2980f csc: 0x0d.6ecc779f itc: 3 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x000d.6ecc779f
0x02 0x0001.011.0002a4e9 0x0080012f.4243.1d ---- 1 fsc 0x004e.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
data_block_dump,data header at 0x14055274
===============
tsiz: 0x1f88
hsiz: 0xba
pbl: 0x14055274
bdba: 0x02c02916
76543210
flag=--------
ntab=1
nrow=84
frre=-1
fsbo=0xba
fseo=0x3eb
avsp=0x331
tosp=0x381
0xe:pti[0] nrow=84 offs=0
0x12:pri[0] offs=0x1f35
0x14:pri[1] offs=0x1eea
0x16:pri[2] offs=0x1e9a
0x18:pri[3] offs=0x1e50
其中Lck 表示锁住的行
从上面我的数据块dump,我们可以发现，该事务所对应的ITL是0x02 .(因为lck是1,flag是---,表示未提交)
Itl Xid Uba Flag Lck Scn/Fsc
0x02 0x0001.011.0002a4e9 0x0080012f.4243.1d ---- 1 fsc 0x004e.00000000
下面开始分解Xid:
0x0001:回滚段编号,转换后为1,说明该事务使用的是第1号回滚段
011:事务槽编号(slot),转换后为17,说明对应undo segment header的transaction table记录中的index是17
0002a4e9:173289序号(同一个事务可能具有多个SCN，实际上每一个DML操作都有一个SCN）实际上对应undo segment header中transaction table的wrap#值
SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec,to_char(START_SCN,'9999999999999') from v$transaction;
XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL UBAREC TO_CHAR(START_
---------- ---------- ---------- ---------- ---------- ---------- --------------
1 17 173289 303 2 29 5769346860
SQL> select name from v$rollname where usn=1;
NAME
------------------------------
_SYSSMU1$
-------undo segment header dump (_SYSSMU8$)
dump 回滚段:
SQL> alter system dump undo header '_SYSSMU1$';
System altered.
********************************************************************************
Undo Segment: _SYSSMU1$ (1)
********************************************************************************
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 4 #blocks: 271
last map 0x00000000 #maps: 0 offset: 4080
Highwater:: 0x00800131 ext#: 2 blk#: 40 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 2
Unlocked
Map Header:: next 0x00000000 #extents: 4 obj#: 0 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x0080000a length: 7
0x00801611 length: 8
0x00800109 length: 128
0x00800789 length: 128
Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1408576610
Extent Number:1 Commit Time: 1408576610
Extent Number:2 Commit Time: 0
Extent Number:3 Commit Time: 1408576610
TRN CTL:: seq: 0x4243 chd: 0x002a ctl: 0x0028 inc: 0x00000000 nfb: 0x0002
mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x0080012f.4243.1d scn: 0x000d.6ecc61a1
Version: 0x01
FREE BLOCK POOL::
uba: 0x00000000.4243.1c ext: 0x2 spc: 0x1166
uba: 0x00800130.4243.02 ext: 0x2 spc: 0x1f06
uba: 0x00800131.4243.16 ext: 0x2 spc: 0xfb6
uba: 0x00000000.423d.01 ext: 0x2 spc: 0x1f88
uba: 0x00000000.2919.01 ext: 0x2 spc: 0x1f88
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x0a 9 0x00 0x2a4f0 0x0008 0x000d.6ecc69cc 0x0080011f 0x0000.000.00000000 0x00000001 0x00000000 1408591580
0x0b 9 0x00 0x2a4e6 0x002f 0x000d.6ecc72a2 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1408597235
0x0c 9 0x00 0x2a4dd 0x000e 0x000d.6ecc6d05 0x0080011f 0x0000.000.00000000 0x00000001 0x00000000 1408593634
0x0d 9 0x00 0x2a4ea 0x0022 0x000d.6ecc6384 0x0080011d 0x0000.000.00000000 0x00000001 0x00000000 1408587630
0x0e 9 0x00 0x2a4ee 0x0017 0x000d.6ecc6e3b 0x0080011e 0x0000.000.00000000 0x00000001 0x00000000 1408594533
0x0f 9 0x00 0x2a4e6 0x000a 0x000d.6ecc694a 0x0080011f 0x0000.000.00000000 0x00000001 0x00000000 1408591232
0x10 9 0x00 0x2a4e1 0x0029 0x000d.6ecc728f 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1408597235
0x11 10 0x80 0x2a4e9 0x0002 0x000d.6ecc77b9 0x0080012f 0x0000.000.00000000 0x00000001 0x00000000 0
0x12 9 0x00 0x2a4f2 0x0028 0x000d.6ecc773f 0x0080012f 0x0000.000.00000000 0x00000001 0x00000000 1408600205
0x13 9 0x00 0x2a4eb 0x002c 0x000d.6ecc68e0 0x0080011f 0x0000.000.00000000 0x00000001 0x00000000 1408591232
0x14 9 0x00 0x2a4eb 0x001c 0x000d.6ecc6360 0x0080011d 0x0000.000.00000000 0x00000001 0x00000000 1408587630
0x15 9 0x00 0x2a4ea 0x000d 0x000d.6ecc6370 0x0080011d 0x0000.000.00000000 0x00000001 0x00000000 1408587630
0x16 9 0x00 0x2a4e9 0x0002 0x000d.6ecc7406 0x0080012f 0x0000.000.00000000 0x00000001 0x00000000 1408597835
0x17 9 0x00 0x2a44f 0x0019 0x000d.6ecc6e4b 0x0080011e 0x0000.000.00000000 0x00000001 0x00000000 1408594533
0x18 9 0x00 0x2a4f0 0x001d 0x000d.6ecc6548 0x0080011f 0x0000.000.00000000 0x00000001 0x00000000 1408588831
state 9标示inactive
10 标示active
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x11 10 0x80 0x2a4e9 0x0002 0x000d.6ecc77b9 0x0080012f 0x0000.000.00000000 0x00000001 0x00000000 0
这一行中的 dba地址 标示的是undo block
0x11 ：17 :事务槽编号(slot),转换后为17,说明对应undo segment header中的transaction table记录中的index是17
########################################################################################################################################
SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec,to_char(START_SCN,'999999999999') from v$transaction;
XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL UBAREC TO_CHAR(START
---------- ---------- ---------- ---------- ---------- ---------- -------------
1 17 173289 303 2 29 57693468601
其中XINUSN 表示回滚段号Undo Segement number
XIDSLOT 事务槽号
XIDSQN 事务编号
UBABLK UBA 块号
dump 数据块:
从上面我的数据块dump,我们可以发现，该事务所对应的ITL是0x02 .(因为lck是1,flag是---,表示未提交)
Itl Xid Uba Flag Lck Scn/Fsc
0x02 0x0001.011.0002a4e9 0x0080012f.4243.1d ---- 1 fsc 0x004e.00000000
下面开始分解Xid:
0x0001:回滚段编号,转换后为1,说明该事务使用的是第1号回滚段
011:事务槽编号(slot),转换后为17,说明对应undo segment header的transaction table记录中的index是17
0002a4e9:173289序号(同一个事务可能具有多个SCN，实际上每一个DML操作都有一个SCN）实际上对应undo segment header中transaction table的wrap#值
数据块Uba 0x0080012f.4243.1d 对应 undo header '_SYSSMU1$' dba 0x0080012f
SQL> select dbms_utility.data_block_address_file(to_number(substr(uba, 3, 8),
'xxxxxxxxxxxx')) as undo_file#,
dbms_utility.data_block_address_block(to_number(substr(uba, 3, 8),
'xxxxxxxxxxxx')) as undo_block,
to_number(substr(uba, 12, 4), 'xxxxxxxxxxxx') undo_sequence,
to_number(substr(uba, 17, 2), 'xxxxxxxxxxxx') undo_record
from (select '0x00c0016e.0085.06' uba from dual); 2 3 4 5 6 7
UNDO_FILE# UNDO_BLOCK UNDO_SEQUENCE UNDO_RECORD
---------- ---------- ------------- -----------
3 366 133 6
uba = Address of the last undo block used + Sequence + Last Entry in UNDO record map
###########################################################################################################################################
我们可以看到transaction table中状态处于active的slot是index 0x11 . 跟我们前面的内容刚好对上.
大家注意看slot 0x11的wrap#值,是不是刚好对上前面的xid中的第3部分内容 ？
cflags 表示什么？ 表示事务的状态,0x80表示未commit
uel 表示什么？ 表示事务当前所处于的extent区域(0x0002 ,转换为10进制后为2)
scn 表示什么？ 该事务开始的scn,等于v$transaction.start_scn。
SQL> select * from v$rollstat where usn=1;
USN LATCH EXTENTS RSSIZE WRITES XACTS GETS
---------- ---------- ---------- ---------- ---------- ---------- ----------
WAITS OPTSIZE HWMSIZE SHRINKS WRAPS EXTENDS AVESHRINK
---------- ---------- ---------- ---------- ---------- ---------- ----------
AVEACTIVE STATUS CUREXT CURBLK
---------- --------------- ---------- ----------
1 1 4 2220032 140460 1 354
0 2220032 0 0 0 0
0 ONLINE 2 40
可以可以发现，uel值正好等于v$rollstat.curext值(Current extent)= 2，0x0002表示第2个extent.
SQL> select owner,segment_name,extent_id,file_id,block_id,blocks from dba_extents where segment_name='_SYSSMU1$';
OWNER SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS
---------- ------------ ---------- ---------- ---------- ----------
SYS _SYSSMU1$ 0 2 9 8
SYS _SYSSMU1$ 1 2 5649 8
SYS _SYSSMU1$ 2 2 265 128
SYS _SYSSMU1$ 3 2 1929 128
BLOCK_ID ：extent的起始块
blocks extent的块数
和如下对应
0x0080000a length: 7
0x00801611 length: 8
0x00800109 length: 128
0x00800789 length: 128
查看创建UNOD的sql：
CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE
'/u03/oradata/jhoa/undotbs01.dbf' SIZE 26214400
AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
ALTER DATABASE DATAFILE
'/u03/oradata/jhoa/undotbs01.dbf' RESIZE 2537553920
SQL> select dbms_utility.data_block_address_file(TO_NUMBER('800109', 'XXXXXXXX')) file_id,
dbms_utility.data_block_address_block(TO_NUMBER('800109',
'XXXXXXXX')) block_id
from dual;
2 3 4
FILE_ID BLOCK_ID
---------- ----------
2 265
SQL> select 265 + 128 from dual;
265+128
----------
393
undo block 303正好落在265 --- 393
这里的地址就是UBA：
TRN CTL:: seq: 0x4243 chd: 0x002a ctl: 0x0028 inc: 0x00000000 nfb: 0x0002
mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x0080012f.4243.1d scn: 0x000d.6ecc61a1
10 标示active
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x11 10 0x80 0x2a4e9 0x0002 0x000d.6ecc77b9 0x0080012f 0x0000.000.00000000 0x00000001 0x00000000 0
SQL> select dbms_utility.data_block_address_file(TO_NUMBER('080012f', 'XXXXXXXX')) file_id,
dbms_utility.data_block_address_block(TO_NUMBER('080012f',
'XXXXXXXX')) block_id
from dual;
2 3 4
FILE_ID BLOCK_ID
---------- ----------
2 303
转换uba的地址后得到的块号是303
