Oracle教程 2023-02-27

Oracle 视图 V$TRANSACTION 官方解释，作用，如何使用详细说明

本站中文解释

**Oracle视图V$TRANSACTION**

V$TRANSACTION是Oracle数据库中的一个可重复使用的虚拟表，它可用于监控当前连接到数据库的所有会话和用户的事务情况。它可以帮助DBA监控当前正在运行的事务，以及某个时刻之后活动会话及事务的情况；并可以查看挂起会话及事务发生的时间以及相关原因。

**V$TRANSACTION的用途**

（1）识别会话和事务。V$TRANSACTION可以帮助识别连接到数据库的活动会话及事务，以及可能导致数据库性能问题的会话和事务的情况。

（2）分析会话和事务的状态。V$TRANSACTION可以帮助排查运行工作量或有关会话和事务的问题。

（3）解决活动会话及事务的问题。通过V$TRANSACTION可以帮助找出状态已锁定但会话还在活动状态的异常会话及事务，以及因调度导致性能问题的会话及事务。

**如何使用V$TRANSACTION**

（1）查看所有正在运行的会话及事务：

“`
SELECT SID,
SERIAL#,
USERNAME,
OSUSER,
TO_CHAR(LOGON_TIME, ‘DD/MM/YYYY HH24:MI:SS’),
PROGRAM,
LOCKWAIT
FROM V$SESSION
“`

（2）检查是否有“死锁”会话：

“`
SELECT SID,OPERATION,LOCKED_MODE
FROM V$LOCKED_OBJECT
WHERE SESSION_ID IN
(SELECT SID FROM V$SESSION WHERE LOCKWAIT!=’None’)
“`

（3）根据会话ID和序列号，检查该会话是否有活动事务：

“`
SELECT *
FROM V$TRANSACTION
WHERE SID=’&SID’
AND SERIAL#=’&SERIAL#’
“`

（4）查看某一会话是否有挂起的事务：

“`
SELECT SID,COMMAND,STATUS
FROM V$TRANSACTION
WHERE SID=’&SID’
AND COMMAND=’HOLD’
“`

另外，还可以通过V$TRANSACTION视图来检查某一指定时间段内的活动会话及事务的情况，以此查看对系统的影响情况。

官方英文解释

V$TRANSACTION lists the active transactions in the system.

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`	`VARCHAR2(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
`CON_ID`	`NUMBER`	The ID of the container to which the data pertains. Possible values include: `0`: This value is used for rows containing data that pertain to the entire CDB. This value is also used for rows in non-CDBs. `1`: This value is used for rows containing data that pertain to only the root n: Where n is the applicable container ID for the rows containing data

数据运维技术 » Oracle 视图 V$TRANSACTION 官方解释，作用，如何使用详细说明

分享到：

本站中文解释

官方英文解释

相关推荐