[oracle@primary ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Feb 8 14:14:46 2014

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> create table tt (id int,name varchar2(20));

Table created.

SQL> insert into tt values(1,'xxxxxxxx');

1 row created.

SQL> commit;

Commit complete.

SQL> update tt set name='yyyyyyyyyyy' where id=1;

1 row updated.

查看v$transaction

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

    XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN
---------- ---------- ---------- ---------- ---------- ----------
         8         24      41293          3       2208       2117

通过v$rollstat,dba_roll_segs信息查出

or

select * from v$rollname where usn=8

undo segment为usn=8,segment_name='_SYSSMU8_2012382730$'

#######################################################################################

dump undo segment header信息

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

System altered.

查看信息:

[oracle@primary trace]$ more wlcsp_ora_23703.trc
Trace file /u01/oracle/diag/rdbms/wlcsp/wlcsp/trace/wlcsp_ora_23703.trc
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_HOME = /u01/oracle/product/11.2.0/dbhome_1
System name:    Linux
Node name:      primary
Release:        2.6.18-164.el5
Version:        #1 SMP Tue Aug 18 15:51:48 EDT 2009
Machine:        x86_64
Instance name: wlcsp
Redo thread mounted by this instance: 1
Oracle process number: 34
Unix process pid: 23703, image: oracle@primary (TNS V1-V3)

*** 2014-02-08 14:25:57.403
*** SESSION ID:(36.491) 2014-02-08 14:25:57.403
*** CLIENT ID:() 2014-02-08 14:25:57.403
*** SERVICE NAME:(SYS$USERS) 2014-02-08 14:25:57.403
*** MODULE NAME:(sqlplus@primary (TNS V1-V3)) 2014-02-08 14:25:57.403
*** ACTION NAME:() 2014-02-08 14:25:57.403
 
 
********************************************************************************
Undo Segment:  _SYSSMU8_2012382730$ (8)
********************************************************************************
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 4      #blocks: 271  
                  last map  0x00000000  #maps: 0      offset: 4080 
      Highwater::  0x00c008b1  ext#: 3      blk#: 49     ext size: 128  
  #blocks in seg. hdr's freelists: 0    
  #blocks below: 0    
  mapblk  0x00000000  offset: 3    
                   Unlocked
     Map Header:: next  0x00000000  #extents: 4    obj#: 0      flag: 0x40000000
  Extent Map
  -----------------------------------------------------------------
   0x00c000f1  length: 7    
   0x00c00130  length: 8    
   0x00c01580  length: 128  
   0x00c00880  length: 128  
 
 Retention Table
  -----------------------------------------------------------
 Extent Number:0  Commit Time: 1391818487
 Extent Number:1  Commit Time: 1391818830
 Extent Number:2  Commit Time: 1391835608
 Extent Number:3  Commit Time: 1391835608
 
  TRN CTL:: seq: 0x0845 chd: 0x0012 ctl: 0x001e inc: 0x00000000 nfb: 0x0001
            mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
            uba: 0x00c008a0.0845.17 scn: 0x0000.02ad47ea
Version: 0x01
  FREE BLOCK POOL::
    uba: 0x00000000.0845.16 ext: 0x3  spc: 0x13be 
    uba: 0x00000000.0845.03 ext: 0x3  spc: 0x1eac 
    uba: 0x00c008b1.0845.18 ext: 0x3  spc: 0x1696 
    uba: 0x00000000.07e8.01 ext: 0x2  spc: 0x1f84 
    uba: 0x00000000.0000.00 ext: 0x0  spc: 0x0    
  TRN TBL::
 
  index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt
  ------------------------------------------------------------------------------------------------
   0x00    9    0x00  0xa152  0x0020  0x0000.02ad48fa  0x00c0089e  0x0000.000.00000000  0x00000001   0x00000000  1391839638
   0x01    9    0x00  0xa151  0x000d  0x0000.02ad4941  0x00c0089e  0x0000.000.00000000  0x00000001   0x00000000  1391839638
   0x02    9    0x00  0xa152  0x001a  0x0000.02ad48f8  0x00c0089d  0x0000.000.00000000  0x00000001   0x00000000  1391839638
   0x03    9    0x00  0xa154  0x0017  0x0000.02ad4ac9  0x00c0089e  0x0000.000.00000000  0x00000001   0x00000000  1391839804
   0x04    9    0x00  0xa14e  0x0006  0x0000.02ad49b1  0x00000000  0x0000.000.00000000  0x00000000   0x00000000  1391839639
   0x05    9    0x00  0xa14c  0x0001  0x0000.02ad4923  0x00c0089e  0x0000.000.00000000  0x00000001   0x00000000  1391839638
   0x06    9    0x00  0xa150  0x0015  0x0000.02ad49fc  0x00c0089e  0x0000.000.00000000  0x00000001   0x00000000  1391839640
   0x07    9    0x00  0xa14d  0x000a  0x0000.02ad4b19  0x00c008a0  0x0000.000.00000000  0x00000001   0x00000000  1391839805
   0x08    9    0x00  0xa14c  0x000c  0x0000.02ad4add  0x00c0089e  0x0000.000.00000000  0x00000001   0x00000000  1391839805
   0x09    9    0x00  0xa152  0x001e  0x0000.02ad4b41  0x00c008a0  0x0000.000.00000000  0x00000001   0x00000000  1391839805
   0x0a    9    0x00  0xa154  0x0014  0x0000.02ad4b23  0x00c008a0  0x0000.000.00000000  0x00000001   0x00000000  1391839805
   0x0b    9    0x00  0xa151  0x0010  0x0000.02ad48ab  0x00c00898  0x0000.000.00000000  0x00000001   0x00000000  1391839637
   0x0c    9    0x00  0xa14d  0x001f  0x0000.02ad4ae7  0x00c008a0  0x0000.000.00000000  0x00000001   0x00000000  1391839805
   0x0d    9    0x00  0xa14f  0x0004  0x0000.02ad498b  0x00c0089e  0x0000.000.00000000  0x00000001   0x00000000  1391839639
   0x0e    9    0x00  0xa14d  0x000b  0x0000.02ad4893  0x00c00898  0x0000.000.00000000  0x00000001   0x00000000  1391839637
   0x0f    9    0x00  0xa14f  0x000e  0x0000.02ad486e  0x00c00898  0x0000.000.00000000  0x00000001   0x00000000  1391839637
   0x10    9    0x00  0xa150  0x0019  0x0000.02ad48c5  0x00c00899  0x0000.000.00000000  0x00000002   0x00000000  1391839637
   0x11    9    0x00  0xa153  0x001b  0x0000.02ad4afb  0x00c008a0  0x0000.000.00000000  0x00000001   0x00000000  1391839805
   0x12    9    0x00  0xa14d  0x001d  0x0000.02ad47fa  0x00c00892  0x0000.000.00000000  0x00000001   0x00000000  1391839631
   0x13    9    0x00  0xa14e  0x0009  0x0000.02ad4b37  0x00c008a0  0x0000.000.00000000  0x00000001   0x00000000  1391839805
   0x14    9    0x00  0xa14e  0x0013  0x0000.02ad4b2d  0x00c008a0  0x0000.000.00000000  0x00000001   0x00000000  1391839805
   0x15    9    0x00  0xa149  0x0003  0x0000.02ad4abf  0x00c0089e  0x0000.000.00000000  0x00000001   0x00000000  1391839804
   0x16    9    0x00  0xa150  0x000f  0x0000.02ad4844  0x00c00898  0x0000.000.00000000  0x00000003   0x00000000  1391839633
   0x17    9    0x00  0xa14f  0x0008  0x0000.02ad4ad3  0x00c0089e  0x0000.000.00000000  0x00000001   0x00000000  1391839805
   0x18   10    0x80  0xa14d  0x0003  0x0000.02ad4c9e  0x00c008a0  0x0000.000.00000000  0x00000001   0x00000000  0
   0x19    9    0x00  0xa14b  0x0002  0x0000.02ad48e3  0x00c00899  0x0000.000.00000000  0x00000001   0x00000000  1391839638
   0x1a    9    0x00  0xa150  0x0000  0x0000.02ad48f9  0x00c0089e  0x0000.000.00000000  0x00000003   0x00000000  1391839638
   0x1b    9    0x00  0xa153  0x0021  0x0000.02ad4b05  0x00c008a0  0x0000.000.00000000  0x00000001   0x00000000  1391839805
   0x1c    9    0x00  0xa14f  0x0005  0x0000.02ad490c  0x00c008b1  0x0000.000.00000000  0x00000013   0x00000000  1391839638
   0x1d    9    0x00  0xa14c  0x0016  0x0000.02ad480c  0x00c00892  0x0000.000.00000000  0x00000001   0x00000000  1391839631
   0x1e    9    0x00  0xa14b  0xffff  0x0000.02ad4c2f  0x00c008a0  0x0000.000.00000000  0x00000001   0x00000000  1391840032
   0x1f    9    0x00  0xa148  0x0011  0x0000.02ad4af1  0x00c008a0  0x0000.000.00000000  0x00000001   0x00000000  1391839805
   0x20    9    0x00  0xa14e  0x001c  0x0000.02ad48fb  0x00c008a0  0x0000.000.00000000  0x00000003   0x00000000  1391839638
   0x21    9    0x00  0xa155  0x0007  0x0000.02ad4b0f  0x00c008a0  0x0000.000.00000000  0x00000001   0x00000000  1391839805
  EXT TRN CTL::
  usn: 8
  sp1:0x00000000 sp2:0x00000000 sp3:0x00000000 sp4:0x00000000
  sp5:0x00000000 sp6:0x00000000 sp7:0x7fff00000000 sp8:0x00000000
  EXT TRN TBL::
  index  extflag    extHash    extSpare1   extSpare2
  ---------------------------------------------------
   0x00  0x00000000 0x00000000 0x00000000  0x00000000
....................................................
   0x21  0x00000000 0x00000000 0x00000000  0x00000000

SQL> select to_number('00c008a0','xxxxxxxxxxx') from dual;

TO_NUMBER('00C008A0','XXXXXXXXXXX')
-----------------------------------
                           12585120

SQL> select dbms_utility.data_block_address_file(12585120) file#, dbms_utility.data_block_address_block(12585120) block  from dual;

     FILE#      BLOCK
---------- ----------
         3       2208

对应相应的undo block id。

上述信息也可以通过内部视图x$ktuxe 查询得到。

#################################################################################

可以通过相应的data block id知道XID.

SQL> /

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) ROWID                      ID NAME
------------------------------------ ------------------------------------ ---------------------------------- ------------------ ---------- --------------------
                                   1                                83953                                  0 AAAWAIAABAAAUfxAAA          1 xxxxxxxx

SQL> alter system dump datafile 1 block 83953;

System altered.

Block header dump:  0x004147f1
 Object id on Block? Y
 seg/obj: 0x16008  csc: 0x00.2ad52ba  itc: 2  flg: O  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.006.0000a095  0x00c006a2.07c1.1c  C---    0  scn 0x0000.02ad4c9e
0x02   0x0008.018.0000a14d  0x00c008a0.0845.17  ----    1  fsc 0x0000.00000000
bdba: 0x004147f1
data_block_dump,data header at 0x2b82d1139a5c
===============
tsiz: 0x1fa0
hsiz: 0x14
pbl: 0x2b82d1139a5c
     76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f7f
avsp=0x1f7a
tosp=0x1f7a
0xe:pti[0]      nrow=1  offs=0
0x12:pri[0]     offs=0x1f7f
block_row_dump:
tab 0, row 0, @0x1f7f
tl: 18 fb: --H-FL-- lb: 0x2  cc: 2
col  0: [ 2]  c1 02
col  1: [11]  79 79 79 79 79 79 79 79 79 79 79
end_of_block_dump
End dump data blocks tsn: 0 file#: 1 minblk 83953 maxblk 83953

itl的数量oracle10g之后为169,10g之前为255.

For an index, the value for initrans applies only to the leaf blocks—each branch block (which includes the root block) will get one ITL entry,

转换0x0008.018.0000a14d 为数字型

SQL> select to_number('0000a14d','xxxxxxxxxxx') from dual;

TO_NUMBER('0000A14D','XXXXXXXXXXX')
-----------------------------------
                              41293

SQL> select to_number('018','xxxxxxxxxxx') from dual;

TO_NUMBER('018','XXXXXXXXXXX')
------------------------------
                            24

转换之后为8,24,41293 刚好对应XIDUSN,XIDSLOT,XIDSQN.

插图: