Oracle 动态视图3 V$SESSION

时间:2024-01-18 21:36:20

  每一个连接到数据库实例中的session都拥有一条记录。包括用户session及后台进程如DBWR,LGWR,arcchiver等

Column Datatype Description
SADDR RAW(4 | 8) Session address
SID NUMBER Session identifier
SERIAL# NUMBER 同一个sid被重用时,serial#会增加
AUDSID NUMBER Auditing session ID
PADDR RAW(4 | 8) 关联v$process的addr字段,可以通过这个字段查处当前session对应操作系统的进程的id
USER# NUMBER 等于dba_users中的user_id。Oracle内部进程的user#为0
USERNAME VARCHAR2(30) 等于dba_users中的username。Oracle内部进程的username为空
COMMAND NUMBER session正在执行的SQL ID。1代表create table,3代表select
OWNERID NUMBER The column contents are invalid if the value is 2147483644. Otherwise, this column contains the identifier of the user who owns the migratable session.
 
For operations using Parallel Slaves, interpret this value as a 4-byte value. The low-order 2 bytes of which represent the session number, and the high-order bytes the instance ID of the query coordinator.
TADDR VARCHAR2(8) 当前的transaction address。可以用来关联v$transaction的addr字段
LOCKWAIT VARCHAR2(8) 可以通过这个字段查询出当前正在等待的锁的相关信息。sid & lockwait与v$lock中的sid & kaddr相对应
STATUS VARCHAR2(8) 用来判断session状态。Active:正执行SQL语句。Inactive:等待操作。Killed:被标注为删除
SERVER VARCHAR2(9) Server type (DEDICATED| SHARED| PSEUDO| NONE)
SCHEMA# NUMBER schema user id。Oracle内部进程的schema#为0。
SCHEMANAME VARCHAR2(30) schema username。Oracle内部进程的schemaname为sys
OSUSER VARCHAR2(30) 客户端操作系统用户名
PROCESS VARCHAR2(12) 客户端process id
MACHINE VARCHAR2(64) 客户端machine name
TERMINAL VARCHAR2(30) 客户端执行的terminal name
PROGRAM VARCHAR2(48) 客户端应用程序。比如ORACLE.EXE (PMON)或者sqlplus.exe
TYPE VARCHAR2(10) session type (background or user)
SQL_ADDRESS RAW(4 | 8) session正在执行的sql statement,和v$sql中的address, hash_value, sql_id, child_number相对应
SQL_HASH_VALUE NUMBER
SQL_ID VARCHAR2(13)
SQL_CHILD_NUMBER NUMBER
PREV_SQL_ADDR RAW(4 | 8) 上一次执行的sql statement
PREV_HASH_VALUE NUMBER
PREV_SQL_ID VARCHAR2(13)
PREV_CHILD_NUMBER NUMBER
FIXED_TABLE_SEQUENCE NUMBER 当session完成一个user call后就会增加的一个数值,也就是说,如果session inactive,它就不会增加。因此可以根据此字段的值变化来监控某个时间点以来的session的性能情况。例如,一个小时以前,某个session的FIXED_TABLE_SEQUENCE是10000,而现在是20000,则表明一个小时内其user call比较频繁,可以重点关注此session的performance statistics
ROW_WAIT_OBJ# NUMBER 被锁定行所在table的object_id。和dba_objects中的object_id关联可以得到被锁定的table name
ROW_WAIT_FILE# NUMBER 被锁定行所在的datafile id。和v$datafile中的file#关联可以得到datafile name
ROW_WAIT_BLOCK# NUMBER Identifier for the block containing the row specified in ROW_WAIT_ROW#. This column is valid only if the session is currently waiting for another transaction to commit and the value of ROW_WAIT_OBJ# is not -1.
ROW_WAIT_ROW# NUMBER session当前正在等待的被锁定的行
LOGON_TIME DATE Time of logon
LAST_CALL_ET NUMBER If the session STATUS is currently ACTIVE, then the value represents the elapsed time in seconds since the session has become active.
If the session STATUS is currently INACTIVE, then the value represents the elapsed time in seconds since the session has become inactive.
PDML_ENABLED VARCHAR2(3) This column has been replaced by column PDML_STATUS
FAILOVER_TYPE VARCHAR2(13) Indicates whether and to what extent transparent application failover (TAF) is enabled for the session:
 
NONE - Failover is disabled for this session
SESSION - Client is able to fail over its session following a disconnect
SELECT - Client is able to fail over queries in progress as well
 
FAILOVER_METHOD VARCHAR2(10) Indicates the transparent application failover method for the session:
 
NONE - Failover is disabled for this session
BASIC - Client itself reconnects following a disconnect
PRECONNECT - Backup instance can support all connections from every instance for which it is backed up
 
FAILED_OVER VARCHAR2(3) Indicates whether the session is running in failover mode and failover has occurred (YES) or not (NO)
RESOURCE_CONSUMER_GROUP VARCHAR2(32) Name of the session's current resource consumer group
PDML_STATUS VARCHAR2(8) If ENABLED, the session is in a PARALLEL DML enabled mode. If DISABLED, PARALLEL DML enabled mode is not supported for the session. If FORCED, the session has been altered to force PARALLEL DML.
PDDL_STATUS VARCHAR2(8) If ENABLED, the session is in a PARALLEL DDL enabled mode. If DISABLED, PARALLEL DDL enabled mode is not supported for the session. If FORCED, the session has been altered to force PARALLEL DDL.
PQ_STATUS VARCHAR2(8) If ENABLED, the session is in a PARALLEL QUERY enabled mode. If DISABLED, PARALLEL QUERY enabled mode is not supported for the session. If FORCED, the session has been altered to force PARALLEL QUERY.
CURRENT_QUEUE_DURATION NUMBER If queued (1), the current amount of time the session has been queued. If not currently queued, the value is 0.
CLIENT_IDENTIFIER VARCHAR2(64) Client identifier of the session
BLOCKING_SESSION_STATUS VARCHAR2(11) Blocking session status:
 
VALID
NO HOLDER
GLOBAL
NOT IN WAIT
UNKNOWN
 
BLOCKING_INSTANCE NUMBER Instance identifier of blocking session
BLOCKING_SESSION NUMBER Session identifier of blocking session
SEQ# NUMBER Sequence number that uniquely identifies the wait. Incremented for each wait.
EVENT# NUMBER Event number
EVENT VARCHAR2(64) Resource or event for which the session is waiting
P1TEXT VARCHAR2(64) Description of the first additional parameter
P1 NUMBER First additional parameter
P1RAW RAW(4) First additional parameter
P2TEXT VARCHAR2(64) Description of the second additional parameter
P2 NUMBER Second additional parameter
P2RAW RAW(4) Second additional parameter
P3TEXT VARCHAR2(64) Description of the third additional parameter
P3 NUMBER Third additional parameter
P3RAW RAW(4) Third additional parameter
WAIT_CLASS_ID NUMBER Identifier of the wait class
WAIT_CLASS# NUMBER Number of the wait class
WAIT_CLASS VARCHAR2(64) Name of the wait class
WAIT_TIME NUMBER WAIT_TIME <> 0  上次实际等待时间;WAIT_TIME=0  session正在等待当前的事件
SECONDS_IN_WAIT NUMBER WAIT_TIME=0  SECONDS_IN_WAIT是在当前等待条件下的等待时间;
WAIT_TIME>0  SECONDS_IN_WAIT是指从上次等待的起始时间到当前时间,SECONDS_IN_WAIT - WAIT_TIME /100 是从上次等待结束时间点到当前时间(即活动时间)
STATE VARCHAR2(19) Wait state:参照WAIT_TIME,SECONDS_IN_WAIT字段
 
0 - WAITING (session正在等待当前的事件)
-2 - WAITED UNKNOWN TIME (duration of last wait is unknown)
-1 - WAITED SHORT TIME (last wait <1/100th of a second)
>0 - WAITED KNOWN TIME (WAIT_TIME = 上次实际等待时间)
SERVICE_NAME VARCHAR2(64) Service name of the session
SQL_TRACE VARCHAR2(8) Indicates whether SQL tracing is enabled (ENABLED) or disabled (DISABLED)
SQL_TRACE_WAITS VARCHAR2(5) Indicates whether wait tracing is enabled (TRUE) or not (FALSE)
SQL_TRACE_BINDS VARCHAR2(5) Indicates whether bind tracing is enabled (TRUE) or not (FALSE)
Session的信息:
SID:SESSION标识,常用于连接其它列
SERIAL#:如果某个SID又被其它的session使用的话则此数值自增加(当一个 SESSION结束,另一个SESSION开始并使用了同一个SID)。
AUDSID:审查session ID唯一性,确认它通常也用于当寻找并行查询模式
USERNAME:当前session在oracle中的用户名。 Client信息:
数据库session被一个运行在数据库服务器上或从中间服务器甚至桌面通过SQL*Net连接到数据库的客户端进程启动,下列各列提供这个客户端的信息
OSUSER:客户端操作系统用户名
MACHINE:客户端执行的机器
TERMINAL:客户端运行的终端
PROCESS:客户端进程的ID
PROGRAM:客户端执行的客户端程序
要显示用户所连接PC的 TERMINAL、OSUSER,需在该PC的ORACLE.INI或Windows中设置关键字TERMINAL,USERNAME。 Application信息:
调用DBMS_APPLICATION_INFO包以设置一些信息区分用户。这将显示下列各列。
CLIENT_INFO:DBMS_APPLICATION_INFO中设置
ACTION:DBMS_APPLICATION_INFO中设置
MODULE:DBMS_APPLICATION_INFO中设置

示例:

 1.查找你的session信息
SQL> SELECT SID, OSUSER, USERNAME, MACHINE, PROCESS
2 FROM V$SESSION WHERE audsid = userenv('SESSIONID'); SID OSUSER USERNAME MACHINE PROCESS
---------- ---------- ---------- --------------- ------------
134 oracle SYS mysolaris 10100
159 Administra SYS WORKGROUP\THINK 4996:5088 2.当machine已知的情况下查找session
SQL> SELECT SID, OSUSER, USERNAME, MACHINE, TERMINAL
2 FROM V$SESSION
3 WHERE machine = 'mysolaris'; SID OSUSER USERNAME MACHINE TERMINAL
---------- ---------- ---------- --------------- ------------------------------
131 oracle mysolaris UNKNOWN
134 oracle SYS mysolaris pts/2
137 oracle mysolaris UNKNOWN
138 oracle mysolaris UNKNOWN
141 oracle mysolaris UNKNOWN
144 oracle mysolaris UNKNOWN
150 oracle mysolaris UNKNOWN
151 oracle mysolaris UNKNOWN
156 oracle mysolaris UNKNOWN
157 oracle mysolaris UNKNOWN
160 oracle mysolaris UNKNOWN SID OSUSER USERNAME MACHINE TERMINAL
---------- ---------- ---------- --------------- ------------------------------
161 oracle mysolaris UNKNOWN
162 oracle mysolaris UNKNOWN
163 oracle mysolaris UNKNOWN
164 oracle mysolaris UNKNOWN
165 oracle mysolaris UNKNOWN
166 oracle mysolaris UNKNOWN
167 oracle mysolaris UNKNOWN
168 oracle mysolaris UNKNOWN
169 oracle mysolaris UNKNOWN
170 oracle mysolaris UNKNOWN 21 rows selected. SQL>

  V$SESSION是基础视图,常常同其他视图协同工作,如:v$lock,v$sqlarea,v$sqltext等