Oracle八大性能视图之v$transaction

 

1、表结构

SQL> desc v$transaction;

 名称                                      是否为空? 类型

 ----------------------------------------- -------- ----------------------------

 ADDR                                               RAW(8)

 XIDUSN                                             NUMBER

 XIDSLOT                                            NUMBER

 XIDSQN                                             NUMBER

 UBAFIL                                             NUMBER

 UBABLK                                             NUMBER

 UBASQN                                             NUMBER

 UBAREC                                             NUMBER

 STATUS                                             VARCHAR2(16)

 START_TIME                                         VARCHAR2(20)

 START_SCNB                                         NUMBER

 START_SCNW                                         NUMBER

 START_UEXT                                         NUMBER

 START_UBAFIL                                       NUMBER

 START_UBABLK                                       NUMBER

 START_UBASQN                                       NUMBER

 START_UBAREC                                       NUMBER

 SES_ADDR                                           RAW(8)

 FLAG                                               NUMBER

 SPACE                                              VARCHAR2(3)

 RECURSIVE                                          VARCHAR2(3)

 NOUNDO                                             VARCHAR2(3)

 PTX                                                VARCHAR2(3)

 NAME                                               VARCHAR2(256)

 PRV_XIDUSN                                         NUMBER

 PRV_XIDSLT                                         NUMBER

 PRV_XIDSQN                                         NUMBER

 PTX_XIDUSN                                         NUMBER

 PTX_XIDSLT                                         NUMBER

 PTX_XIDSQN                                         NUMBER

 DSCN-B                                             NUMBER

 DSCN-W                                             NUMBER

 USED_UBLK                                          NUMBER

 USED_UREC                                          NUMBER

 LOG_IO                                             NUMBER

 PHY_IO                                             NUMBER

 CR_GET                                             NUMBER

 CR_CHANGE                                          NUMBER

 START_DATE                                         DATE

 DSCN_BASE                                          NUMBER

 DSCN_WRAP                                          NUMBER

 START_SCN                                          NUMBER

 DEPENDENT_SCN                                      NUMBER

 XID                                                RAW(8)

 PRV_XID                                            RAW(8)

 PTX_XID                                            RAW(8)

 

 2、根据session id可以查到当前session正在执行的事务信息

 select * from v$transaction where addr in (select taddr from v$session where sid=&sid );

 

 

 看下面这两个字段可以看到事务进行到什么程度了：

USED_UBLK

NUMBER

Number of undo blocks used

USED_UREC

NUMBER

Number of undo records used

重复查询这两个值，可以看到变化，可以估计事务的进度，尤其是长时间的回滚操作，当这两个值为0，回滚也就完成了。

如SQL语句如下：

select a.sid,

       a.serial#,

       a.user#,

       a.username,

       b.addr,

       b.USED_UBLK,

       b.USED_UREC

  from v$transaction b, v$session a

 where b.addr in (select a.taddr from v$session a where a.sid = &sid)