v$transaction
V$TRANSACTION lists the active transactions in the system. When the transaction is complete, (either COMMIT or ROLLBACK), the entry should go away.
| Column | Datatype | Description |
|---|---|---|
| ADDR | RAW(4 | 8) | Address of the transaction state object |
| XIDUSN | NUMBER | Undo segment number |
| XIDSLOT | NUMBER | Slot number |
| XIDSQN | NUMBER | Sequence number |
| UBAFIL | NUMBER | Undo block address (UBA) filenum |
| UBABLK | NUMBER | UBA block number |
| UBASQN | NUMBER | UBA sequence number |
| UBAREC | NUMBER | UBA record number |
| STATUS | VARCHAR2(16) | Status |
| START_TIME | VARCHAR2(20) | Start time (wall clock) |
| START_SCNB | NUMBER | Start system change number (SCN) base |
| START_SCNW | NUMBER | Start SCN wrap |
| START_UEXT | NUMBER | Start extent number |
| START_UBAFIL | NUMBER | Start UBA file number |
| START_UBABLK | NUMBER | Start UBA block number |
| START_UBASQN | NUMBER | Start UBA sequence number |
| START_UBAREC | NUMBER | Start UBA record number |
| SES_ADDR | RAW(4 | 8) | User session object address |
| FLAG | NUMBER | Flag |
| SPACE | VARCHAR2(3) | YES if a space transaction |
| RECURSIVE | VARCHAR2(3) | YES if a recursive transaction |
| NOUNDO | VARCHAR2(3) | YES if a no undo transaction |
| PTX | VARCHAR 2(3) | YES if parallel transaction |
| NAME | VARCHAR2(256) | Name of a named transaction |
| PRV_XIDUSN | NUMBER | Previous transaction undo segment number |
| PRV_XIDSLT | NUMBER | Previous transaction slot number |
| PRV_XIDSQN | NUMBER | Previous transaction sequence number |
| PTX_XIDUSN | NUMBER | Rollback segment number of the parent XID |
| PTX_XIDSLT | NUMBER | Slot number of the parent XID |
| PTX_XIDSQN | NUMBER | Sequence number of the parent XID |
| DSCN-B | NUMBER | This column is obsolete and maintained for backward compatibility. The value of this column is always equal to the value in DSCN_BASE. |
| DSCN-W | NUMBER | This column is obsolete and maintained for backward compatibility. The value of this column is always equal to the value in DSCN_WRAP. |
| USED_UBLK | NUMBER | Number of undo blocks used |
| USED_UREC | NUMBER | Number of undo records used |
| LOG_IO | NUMBER | Logical I/O |
| PHY_IO | NUMBER | Physical I/O |
| CR_GET | NUMBER | Consistent gets |
| CR_CHANGE | NUMBER | Consistent changes |
| START_DATE | DATE | Start time (wall clock) |
| DSCN_BASE | NUMBER | Dependent SCN base |
| DSCN_WRAP | NUMBER | Dependent SCN wrap |
| START_SCN | NUMBER | Start SCN |
| DEPENDENT_SCN | NUMBER | Dependent SCN |
| XID | RAW(8) | Transaction XID |
| PRV_XID | RAW(8) | Previous transaction XID |
| PTX_XID | RAW(8) | Parent transaction XID |
示例一：
session A:
创建测试表，并更新
SQL> create table scott.test as select * from dba_objects;
SQL> update test set OBJECT_ID=OBJECT_ID+1;
已更新50343行。
session B:
查询v$transaction
SQL> select START_TIME,START_SCNB,USED_UBLK,USED_UREC,LOG_IO,PHY_IO
2 from v$transaction;
START_TIME START_SCNB USED_UBLK USED_UREC LOG_IO PHY_IO
--------------- ---------- ---------- --------- ------- ----------
05/05/08 15:13:53 708551 698 54422 185843 0
session A:
再更新一些记录
SQL> update test set wner='AAA';
已更新50343行。
session B:
再查询v$transaction
SQL> /
START_TIME START_SCNB USED_UBLK USED_UREC LOG_IO PHY_IO
---------------- ---------- --------- --------- ------- ----------
05/05/08 15:13:53 708551 1269 96902 353324 1
列出这几列的含义，自己可以对比一下：
start_time --> start_time :)
start_scnb --> 开始的scn
used_ublk --> 占用的undo block
used_urec --> undo 记录的行数
log_io --> 逻辑io 注意并非Consistent gets，有专门一列：CR_GET
phy_io --> 物理io
如果想要得到session那就可以再加上查询SES_ADDR列：
SES_ADDR User session object address -->对应v$session 的saddr列，再进一步自然可以找到正在执行的sql.
如果想要对应回滚段则：
XIDUSN Undo segment number -->使用的回滚段id,可以和v$rollstat对应
想更深入了解一个transaction,有了sql就能更深入了。
v$session a, v$transaction b
WHERE a.saddr = b.ses_addr;
---------- ------------------------------ ---------- ---------- ----------
118 CCP 102 12 1
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr;
--------- ------------------------------ ---------- ---------- ----------
596 GCC 87 2 1