Check Transaction Table-优快云博客

本文详细解析了Oracle中X$KTUXE视图用于事务跟踪和分析的方法，包括如何使用SQL查询获取特定事务的详细信息，如事务ID、USN、slot等，以及通过查看这些信息来理解事务的状态和操作过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

首先看一下x$ktuxe的定义：

X$KTUXE [K]ernel [T]ransaction [U]ndo Transa[x]tion [E]ntry (table)

Column          Type               Description
 --------        ----               --------
 ADDR            RAW(4|8)           address of this row/entry in the array or SGA
 INDX            NUMBER             index number of this row in the fixed table array
 INST_ID         NUMBER       8.x   oracle instance number
Transaction ID
 KTUXEUSN        NUMBER             undo seg number
 KUSNOLTP        UB2MAXVAL          is no-undo xac
 KTUXESLT        NUMBER             slot number
 KSLTINV         UB2MAXVAL          not a valid slot num
 KTUXESQN        NUMBER             wrap number, is savept # at start if no-undo xac
                                    position within transaction
 KTUXERDBF       NUMBER             relative File
 KTUXERDBB       NUMBER             relative Block
 KTUXESCNB       NUMBER             SCN base for prepare/commit
 KTUXESCNW       NUMBER             SCN wrap for prepare/commit
 KTUXESTA        VARCHAR2(16)       Transaction Status
 KTUXECFL        VARCHAR2(24)       Transaction flags
 KTUXEUEL        NUMBER             Used for extent of tx and link to commit list
查看相关的事务：

select xidusn,xidslot
from v$transaction 
where addr=(select taddr from v$session
            where sid=953);
xidusn  xidslot
----    ------
61      35

我们不用通过dump来查看transaction table的相关信息，可以通过查看x$ktuxe视图来查看：

SQL> col indexe for a10
SQL> col nub for 999999
SQL> select
  2  indx,
  3  ktuxesta,
  4  ktuxecfl,
  5  ktuxesqn wrap#,
  6  ktuxescnw scnW,
  7  ktuxescnb scnB,
  8  ktuxerdbf dba_file,
  9  ktuxerdbb dba_block,
 10  ktuxesiz nub
 11  from
 12  x$ktuxe where ktuxeusn =61 --USN
 13  and ktuxeslt <=35 --slot; 

INDX KTUXESTA    KTUXEC      WRAP#       SCNW       SCNB   DBA_FILE  DBA_BLOCK     NUB
----- ----------- ------ ---------- ---------- ---------- ---------- ---------- -------
    0 INACTIVE    NONE     22122484       2890 1643994581        613     894448       1
    1 INACTIVE    NONE     22124822       2890 1058946158        613     894438       1
    2 INACTIVE    NONE     22124065       2890 1673655267        613     894448       1
    3 INACTIVE    NONE     22123988       2890 1518121267        613     894439       1
    4 INACTIVE    NONE     22123495       2890 1518120985        613     894439       1
    5 INACTIVE    NONE     22122958       2890 1337345036        613     894438       1
    6 INACTIVE    NONE     22122999       2890 1478607314        613     894438       1
    7 INACTIVE    NONE     22115797       2890 1804211077        613     894510       1
    8 INACTIVE    NONE     22124019       2890 1632919706        613     894443       3
    9 INACTIVE    NONE     22124580       2890 1464090318        613     894438       1
   10 INACTIVE    NONE     22124030       2890 1701472974        613     894480      32
   11 INACTIVE    NONE     22123412       2890 1488238170        613     894439       1
   12 INACTIVE    NONE     22124685       2890 1475657118        613     894438       1
   13 INACTIVE    NONE     22122101       2890 1643992869        613     894448       1
   14 INACTIVE    NONE     22094006       2890 1769952615        613     894510      31
   15 INACTIVE    NONE     22121871       2890 1743673582        613     894480       1
   16 INACTIVE    NONE     22123393       2890  931825045        613     894438       1
   17 INACTIVE    NONE     22124418       2890 1478607907        613     894440       2
   18 INACTIVE    NONE     22124323       2890 1297504055        613     894438       1
   19 INACTIVE    NONE     22123510       2890  984809279        613     894438       1
   20 INACTIVE    NONE     22124197       2890 1495948955        613     894439       1
   21 INACTIVE    NONE     22124687       2890 1758044606        613     894480       1
   22 INACTIVE    NONE     22124883       2890 1632919727        613     894448       3
   23 INACTIVE    NONE     22123262       2890 1495948866        613     894439       1
   24 INACTIVE    NONE     22123397       2890 1636431267        613     894448       1
   25 INACTIVE    NONE     22121784       2890 1664612203        613     894448       1
   26 INACTIVE    NONE     22123760       2890 1827875721        613     894449       1
   27 INACTIVE    NONE     22123940       2890 1478607546        613     894438       1
   28 INACTIVE    NONE     22123746       2890 1751598900        613     894480       1
   29 INACTIVE    NONE     22123079       2890 1643993570        613     894448       1
   30 INACTIVE    NONE     22123632       2890 1867345324        624     911099       1
   31 INACTIVE    NONE     22124222       2890 1058946229        613     894438       1
   32 INACTIVE    NONE     22124168       2890 1664612101        613     894448       1
   33 INACTIVE    NONE     22123525       2890 1632919729        613     894449       4
   34 INACTIVE    NONE     22123112       2890 1743673650        613     894480       1
   35 INACTIVE    NONE     22122741       2890 1842256156        624     911099  243138

仅记之.......