【Oracle会话】V$SESSION视图

【Oracle会话】V$SESSION视图 详解

V$SESSION是基础信息视图，视图主要提供的是一个数据库connect的信息，主要是client端的信息，用于找寻用户SID或SADDR。

不过，它也有一些列会动态的变化，可用于检查用户。在该视图中，每一个连接到数据库实例中的session都拥有一条记录，包括用户session及后台进程如DBWR，LGWR，arcchiver等等。

select * from v$session;

V$SESSION视图在Oracle 11gR2下包含97列，在Oracle 12cR2下增加了6列，共包含103列，字段说明如下

【Oracle会话】V$SESSION视图

	Column	Datatype	Description
1	SADDR	RAW(4 | 8)	Session address 会话地址，对应于V$TRANSACTION.SES_ADDR列。
2	SID	NUMBER	Session identifier 会话标识符。
3	SERIAL#	NUMBER	Session serial number. Used to uniquely identify a session’s objects. Guarantees that session-level commands are applied to the correct session objects if the session ends and another session begins with the same session ID. 会话序列号，用来唯一地标识会话对象。如果该会话结束且其它会话以相同的会话ID开始，那么可以保证会话级的命令被应用到正确的会话对象。
4	AUDSID	NUMBER	Auditing session ID 审计会话ID，审查SESSION ID的唯一性，通常也用于寻找并行查询模式。 SELECT SID, OSUSER, USERNAME, MACHINE, PROCESS FROM V$SESSION WHERE AUDSID = USERENV(‘SESSIONID’); SELECT USERENV(‘LANGUAGE’) 数据库字符集, USERENV(‘ISDBA’) 是否DBA角色, USERENV(‘SESSIONID’) 当前会话标识符, USERENV(‘ENTRYID’) 可审计的会话标识符, USERENV(‘LANG’) 会话语言名称的ISO简记, USERENV(‘INSTANCE’) 当前的实例, USERENV(‘TERMINAL’) 当前计算机名 FROM DUAL;
5	PADDR	RAW(4 | 8)	Address of the process that owns the session 拥有这个会话的进程地址，对应于VKaTeX parse error: Expected 'EOF', got '#' at position 55: …B.SID, B.SERIAL#̲, C.SPID FROM …SESSION B, V$PROCESS C WHERE B.PADDR = C.ADDR;
6	USER#	NUMBER	Oracle user identifier Oracle用户标识符。
7	USERNAME	VARCHAR2(30)	Oracle username Oracle用户名。
8	COMMAND	NUMBER	Command in progress (last statement parsed). You can find the command name for any value n returned in this COMMAND column by running this SQL query: SELECT command_name FROM v$sqlcommandWHEREcomman{d}_{t}ype=n;Avalueof0inthisCOMMANDcolumnmeansthecommandisnotrecordedinV$SESSION. 正在执行的SQL语句类型（分析的最后一个语句）。关于该列值的含义，请参阅V$SQLCOMMAND.COMMAND列。如果该列的值为0，那么表示并没有在V$SESSION视图里记录。
9	OWNERID	NUMBER	Identifier of the user who owns the migratable session; the column contents are invalid if the value is 2147483644 For operations using Parallel Slaves, interpret this value as a 4-byte value. The low-order 2 bytes represent the session number and the high-order bytes represent the instance ID of the query coordinator. 如果值为2147483644，那么此列的内容无效,否则此列包含拥有可移植会话的用户标符。对于利用并行从服务器的操作，将这个值解释为一个4字节的值，其低位两字节表示会话号，而高位字节表示查询协调程序的实例ID。
10	TADDR	VARCHAR2(8)	Address of the transaction state object 表示事务处理状态对象的地址，对应于V$TRANSACTION.ADDR列。
11	LOCKWAIT	VARCHAR2(8)	Address of the lock the session is waiting for; NULL if none 等待锁的地址，对应于V$LOCK的KADDR列；若当前会话没有被阻塞则为空
12	STATUS	VARCHAR2(8)	Status of the session: ACTIVE - Session currently executing SQL INACTIVE - Session which is inactive and either has no configured limits or has not yet exceeded the configured limits KILLED - Session marked to be killed CACHED - Session temporarily cached for use by Oracle*XA SNIPED - An inactive session that has exceeded some configured limits (for example, resource limits specified for the resource manager consumer group or idle_time specified in the user’s profile). Such sessions will not be allowed to become active again. 会话的状态： ACTIVE：当前正在执行SQL语句（waiting for/using a resource）； INACTIVE：等待操作（即等待需要执行的SQL语句）； KILLED：标记为终止，删除； CACHED：为Oracle*XA使用而临时高速缓存； SNIPED：会话不活动，在客户机上等待，该状态不再被允许变为ACTIVE。
13	SERVER	VARCHAR2(9)	Server type: DEDICATED 、SHARED 、PSEUDO 、POOLED 、NONE 表示服务器类型：DEDICATED、SHARED、PSEUDO、POOLED、NONE。 若显示的结果中有NONE或SHARED，则说明当前数据库启动了共享服务器模式。其中，状态为NONE的会话表示空闲的共享服务器连接，说明当前会话没有处理任务；若进程正在执行某些任务则会表现为SHARED状态。PSEUDO和“ALTER SYSTEM KILL SESSION sid,serial#;”相关，如果发出ALTER SYSTEM去终止一个会话，被标记为终止的SESSION在V$SEESSION的STATUS为KILLED，对应的SERVER值为PSEUDO。POOLED和Oracle 11g推出的驻留连接池（Database Resident Connection Pool）特性相关，驻留连接池提供了数据库层面上的连接池管理机制，为应对高并发、短会话前端应用进行有益的尝试，POOLED表示使用驻留连接池技术的连接。
14	SCHEMA#	NUMBER	Schema user identifier 模式用户标识符。
15	SCHEMANAME	VARCHAR2(30)	Schema user name 模式用户名。
16	OSUSER	VARCHAR2(30)	Operating system client user name 操作系统客户端用户名。
17	PROCESS	VARCHAR2(24)	Operating system client process ID 操作系统的客户端进程ID，即客户端进程在客户端机器上的进程ID号；V$PROCESS中的SPID表示的是操作系统的进程ID，即服务器进程在服务器上的进程ID。若客户端为Windows系统，则该列的值格式为“客户端进程ID:线程ID”。对于后台进程而言（TYPE{=}^{\prime }BACKGROUN{D}^{\prime }），该列的值和V$PROCESS中的SPID的值是一致的。
18	MACHINE	VARCHAR2(64)	Operating system machine name 操作系统机器名。
19	PORT	NUMBER	Client port number 客户端端口号。
20	TERMINAL	VARCHAR2(30)	Operating system terminal name 操作系统终端名。
21	PROGRAM	VARCHAR2(48)	Operating system program name 操作系统程序名。
22	TYPE	VARCHAR2(10)	Session type 会话类型，BACKGROUND表示后台进程，USER表示用户进程。
23	SQL_ADDRESS	RAW(4 | 8)	Used with SQL_HASH_VALUE to identify the SQL statement that is currently being executed 与SQL_HASH_VALUE一道使用标识当前正在执行的SQL语句。
24	SQL_HASH_VALUE	NUMBER	Used with SQL_ADDRESS to identify the SQL statement that is currently being executed 与SQL_ADDRESS一道使用标识当前正在执行的SQL语句。
25	SQL_ID	VARCHAR2(13)	SQL identifier of the SQL statement that is currently being executed 正在执行的SQL语句ID
26	SQL_CHILD_NUMBER	NUMBER	Child number of the SQL statement that is currently being executed 正在执行的SQL语句的子游标的ID，从0开始
27	SQL_EXEC_START	DATE	Time when the execution of the SQL currently executed by this session started; NULL if SQL_ID is NULL 该会话开始执行该SQL语句的时间。若SQL_ID为空，则该列为空。
28	SQL_EXEC_ID	NUMBER	SQL execution identifier; NULL if SQL_ID is NULL or if the execution of that SQL has not yet started (see V$SQ{L}_{M}ONITOR)<br/>SQL执行的标识符ID。若SQ{L}_{I}D为空或SQL还未开始执行，则该列为空。对应于V$SQL_MONITOR.SQL_EXEC_ID列。
29	PREV_SQL_ADDR	RAW(4 | 8)	Used with PREV_HASH_VALUE to identify the last SQL statement executed 与PREV_HASH_VALUE一起使用标识上一次执行的SQL语句。
30	PREV_HASH_VALUE	NUMBER	Used with SQL_HASH_VALUE to identify the last SQL statement executed 与SQL_HASH_VALUE一起使用标识上一次执行的SQL语句。
31	PREV_SQL_ID	VARCHAR2(13)	SQL identifier of the last SQL statement executed 上一次执行的SQL语句ID。
32	PREV_CHILD_NUMBER	NUMBER	Child number of the last SQL statement executed 上一次执行的SQL语句的子游标ID。
33	PREV_EXEC_START	DATE	SQL execution start of the last executed SQL statement 上一次执行SQL语句的开始时间。
34	PREV_EXEC_ID	NUMBER	SQL execution identifier of the last executed SQL statement 上一次执行SQL语句的执行ID。对应于V$SQL_MONITOR.SQL_EXEC_ID列。
35	PLSQL_ENTRY_OBJECT_ID	NUMBER	Object ID of the top-most PL/SQL subprogram on the stack; NULL if there is no PL/SQL subprogram on the stack 栈上最顶层PL/SQL子程序的对象ID;如果堆栈上没有PL/SQL子程序，则为NULL
36	PLSQL_ENTRY_SUBPROGRAM_ID	NUMBER	Subprogram ID of the top-most PL/SQL subprogram on the stack; NULL if there is no PL/SQL subprogram on the stack 栈上最顶层PL/SQL子程序的子程序ID;如果堆栈上没有PL/SQL子程序，则为NULL
37	PLSQL_OBJECT_ID	NUMBER	Object ID of the currently executing PL/SQL subprogram; NULL if executing SQL 当前正在执行的PL/SQL子程序的对象ID;执行SQL时为NULL
38	PLSQL_SUBPROGRAM_ID	NUMBER	Subprogram ID of the currently executing PL/SQL object; NULL if executing SQL 当前执行PL/SQL对象的子程序ID;执行SQL时为NULL
39		VARCHAR2(48)	Name of the currently executing module as set by calling the DBMS_APPLICATION_INFO.SET_MODULE procedure 包含当前正在执行的模块名，正如由调用DBMS_APPLICATION_INFO.SET_MODULE过程所设置MODULE_HASHNUMBER上面MODULE的散列值。
40	MODULE_HASH	NUMBER	Hash value of the MODULE column MODULE列的HASH值。
41		VARCHAR2(32)	Name of the currently executing action as set by calling the DBMS_APPLICATION_INFO.SET_ACTION procedure 包含当前执行活动的名称，正如由调用DBMS_APPLICATION_INFO.SET_ACTION过程所设置ACTION_HASHNUMBER上列活动名称的散列值。
42	ACTION_HASH	NUMBER	Hash value of the ACTION column ACTION列的HASH值。
43	CLIENT_INFO	VARCHAR2(64)	Information set by the DBMS_APPLICATION_INFO.SET_CLIENT_INFO procedure 由DBMS_APPLICATION_INFO.SET_CLIENT_INFO过程设置的信息。
44	FIXED_TABLE_SEQUENCE	NUMBER	This contains a number that increases every time the session completes a call to the database and there has been an intervening select from a dynamic performance table. This column can be used by performance monitors to monitor statistics in the database. Each time the performance monitor looks at the database, it only needs to look at sessions that are currently active or have a higher value in this column than the highest value that the performance monitor saw the last time. All the other sessions have been idle since the last time the performance monitor looked at the database. 此列包含一个数，每当会话完成一个数据库调用并且存在来自动态性能表的介入选择，它个数就增加。这个列可被性能监控程序用来监控数据库中的统计数据。每当性能监控程序查看数据库时，只需要查看当前活动的会话或在这个列中具有比上次性能监控程序所看到的最大值更大的值的会话即可。所有其他会话自上次性能监控程序查看数据库以来都是空闲的。 当session完成一个user call后就会增加的一个数值，也就是说，如果session inactive，它就不会增加。因此可以根据此字段的值变化来监控某个时间点以来的session的性能情况。例如，一个小时以前，某个session的FIXED_TABLE_SEQUENCE是10000，而现在是20000，则表明一个小时内其user call比较频繁，可以重点关注此session的performance statistics。
45	ROW_WAIT_OBJ#	NUMBER	Object ID for the table containing the row specified in ROW_WAIT_ROW# 被锁定行所在TABLE的OBJECT_ID，该列和DBA_OBJECTS中的OBJECT_ID关联可以得到被锁定的表名。
46	ROW_WAIT_FILE#	NUMBER	Identifier for the datafile 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. 被锁定行所在的数据文件号，该列和v$datafile中的file#关联可以得到数据文件名。此列仅在会话当前正在等待其它事务处理提交并且ROW_WAIT_OBJ#不为-1时有效。
47	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_OBJ#不为-1时有效。
48	ROW_WAIT_ROW#	NUMBER	Current row being locked. 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_OBJ#不为-1时有效。 可以通过ROW_WAIT_OBJ#、ROW_WAIT_FILE#、ROW_WAIT_BLOCK#和ROW_WAIT_ROW#这4个字段查询现在正在被锁的表的相关信息（ROWID），例如，表名、文件名及行号。 SELECT DBMS_ROWID.ROWID_CREATE(1, (SELECT DATA_OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_ID = ROW_WAIT_OBJ#), ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#), A.ROW_WAIT_OBJ#, A.ROW_WAIT_FILE#, A.ROW_WAIT_BLOCK#, A.ROW_WAIT_ROW#, (SELECT D.OWNER || ‘.’ || D.OBJECT_NAME FROM DBA_OBJECTS D WHERE OBJECT_ID = ROW_WAIT_OBJ#) OBJECT_NAME FROM V$SESSION A WHERE A.ROW_WAIT_OBJ# <> -1;
49	TOP_LEVEL_CALL#	NUMBER	Oracle top level call number Oracle顶层调用号
50	LOGON_TIME	DATE	Time of logon 用户的登录时间
51	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. 用户最后一条语句执行完毕后到sysdate的时间，单位为秒。每次用户执行一个新的语句后，该字段复位为0，重新开始记数。可以通过该字段来获得一个连接用户最后一次操作数据库后的空闲时间。
52	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 See Also: Oracle Database Concepts for more information on TAF Oracle Database Net Services Administrator’s Guide for information on configuring TAF 表示TAF的类型， 1.SESSION：表示在故障切换发生后，新的连接会被创建到正常实例，问题出现时正在运行的操作不会被继续执行。 2.SELECT：表示在故障切换发生后，新的连接会被创建到正常实例，问题出现时正在运行的SELECT语句会被继续执行，在新的节点上继续返回后续结果集，而已经返回的记录集则抛弃。 3.NONE：表示不会发生故障切换，即禁用TAF。
53	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 指定TAF的方法，可选项有BASIC（数据库会在故障切换时在目标实例中创建会话）和PRECONNECT（数据库会在最初建立连接时就同时建立到所有实例的连接，当发生故障时就可以立刻切换到其它链路上，这会对目标实例产生额外的工作负载）。需要注意的是，PRECONNECT选项只能用于客户端的TAF配置，不能用于服务端的TAF配置。BASIC方式在Failover时会有延迟，PRECONNECT方式虽然没有时间延迟，但是会建立多个冗余连接会消耗更多资源，两者就是用时间换资源和用资源换时间的区别。
54	FAILED_OVER	VARCHAR2(3)	Indicates whether the session is running in failover mode and failover has occurred (YES) or not (NO) 若当前会话运行在故障切换模式并进行过故障切换，则为TRUE，否则为FALSE。
55	RESOURCE_CONSUMER_GROUP	VARCHAR2(32)	Name of the session’s current resource consumer group 会话的当前资源使用者组的名称
56	PDML_ENABLED	VARCHAR2(3)	This column has been replaced by the PDML_STATUS column 此列已被PDML_STATUS所替代。
57	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. 如果ENABLED，那么会话正处于PARALLEL DML启用方式。如果DISABLED，那么此会话不支持PARALLEL DML启用方式。如果FORCED，那么会话已经更改为强制PARALLEL DML。
58	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. 如果ENABLED，那么会话正处于PARALLEL DDL启用方式。如果DISABLED，那么此会话不支持PARALLEL DDL启用方式。如果FORCED，那么会话已经更改为强制PARALLEL DDL。
59	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. 如果ENABLED，那么会话正处于PARALLEL QUERY启用方式。如果DISABLED，那么此会话不支持PARALLEL QUERY启用方式。如果FORCED，那么会话已经更改为强制PARALLEL QUERY。
60	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. 如果排队(1)，则表示当前会话已排队的时间。如果当前没有排队，则该值为0。
61	CLIENT_IDENTIFIER	VARCHAR2(64)	Client identifier of the session 会话的客户端标识。
62	BLOCKING_SESSION_STATUS	VARCHAR2(11)	This column provides details on whether there is a blocking session: VALID - there is a blocking session, and it is identified in the BLOCKING_INSTANCE and BLOCKING_SESSION columns NO HOLDER - there is no session blocking this session NOT IN WAIT - this session is not in a wait UNKNOWN - the blocking session is unknown 标识当前会话是否被阻塞。VALID表示当前会话被阻塞，可以通过BLOCKING_INSTANCE和 BLOCKING_SESSION列查找到阻塞会话；“NO HOLDER”表示没有被阻塞；“NOT IN WAIT”表示当前会话未等待；UNKNOWN表示未知。
63	BLOCKING_INSTANCE	NUMBER	Instance identifier of the blocking session. This column is valid only if BLOCKING_SESSION_STATUS has the value VALID. 当BLOCKING_SESSION_STATUS的值为VALID时，该列表示阻塞会话的实例号（Instance Number）。
64	BLOCKING_SESSION	NUMBER	Session identifier of the blocking session. This column is valid only if BLOCKING_SESSION_STATUS has the value VALID. 当BLOCKING_SESSION_STATUS的值为VALID时，该列表示阻塞会话的SID。
65	FINAL_BLOCKING_SESSION_STATUS	VARCHAR2(11)	The final blocking session is the final element in the wait chain constructed by following the sessions that are blocked by one another starting with this session. In the case of a cyclical wait chain, one of the sessions in the wait chain will be chosen as the final blocker. This column provides details on whether there is a final blocking session: VALID - there is a final blocking session and it is identified in the FINAL_BLOCKING_INSTANCE and FINAL_BLOCKING_SESSION columns NO HOLDER - there is no session blocking this session NOT IN WAIT - this session is not in a wait UNKNOWN - the final blocking session is unknown 最后一个阻塞会话是等待链中的最后一个元素，它由从这个会话开始的被另一个会话阻塞的会话组成。在循环等待链的情况下，等待链中的一个会话将被选择为最后的阻塞器。此列提供了是否存在最终阻塞会话的详细信息:VALID—存在最终阻塞会话，并在FINAL_BLOCKING_INSTANCE和FINAL_BLOCKING_SESSION列中标识。NO HOLDER—没有会话阻塞此会话。NOT in WAIT—此会话不在等待中
66	FINAL_BLOCKING_INSTANCE	NUMBER	Instance identifier of the final blocking session. This column is valid only if FINAL_BLOCKING_SESSION_STATUS has the value VALID. 最终阻塞会话的实例标识符。只有FINAL_BLOCKING_SESSION_STATUS的值为valid时，此列才有效。
67	FINAL_BLOCKING_SESSION	NUMBER	Session identifier of the blocking session. This column is valid only if FINAL_BLOCKING_SESSION_STATUS has the value VALID. 阻断会话的会话标识。只有FINAL_BLOCKING_SESSION_STATUS的值为valid时，此列才有效。
68	SEQ#	NUMBER	A number that uniquely identifies the current or last wait (incremented for each wait) 唯一标识当前或最后一次等待的数字(每次等待递增)
69	EVENT#	NUMBER	Event number 等待事件的事件号，对应于V$EVENT_NAME.EVENT#列。
70	EVENT	VARCHAR2(64)	Resource or event for which the session is waiting See Also: Appendix C, “Oracle Wait Events” 等待事件的名称，若为空则表示ON CPU。
71	P1TEXT	VARCHAR2(64)	Description of the first wait event parameter 第一个等待事件参数的描述
72	P1	NUMBER	First wait event parameter (in decimal) 第一个等待事件参数(十进制)
73	P1RAW	RAW(8)
74	P2TEXT	VARCHAR2(64)	Description of the second wait event parameter 第二个等待事件参数的描述
75	P2	NUMBER	Second wait event parameter (in decimal) 第二个等待事件参数(十进制)
76	P2RAW	RAW(8)
77	P3TEXT	VARCHAR2(64)	Description of the third wait event parameter 第三个等待事件参数的描述
78	P3	NUMBER	Third wait event parameter (in decimal) 第三个等待事件参数(十进制)
79	P3RAW	RAW(8)
80	WAIT_CLASS_ID	NUMBER	Identifier of the class of the wait event 等待事件类的标识符
81	WAIT_CLASS#	NUMBER	Number of the class of the wait event 等待事件的类的编号
82	WAIT_CLASS	VARCHAR2(64)	Name of the class of the wait event 等待事件的类的名称
83	WAIT_TIME	NUMBER	If the session is currently waiting, then the value is 0. If the session is not in a wait, then the value is as follows: > 0 - Value is the duration of the last wait in hundredths of a second -1 - Duration of the last wait was less than a hundredth of a second -2 - Parameter TIMED_STATISTICS was set to false This column has been deprecated in favor of the columns WAIT_TIME_MICRO and STATE. 如果会话当前处于等待状态，则该值为0。如果会话不处于等待状态，则该值如下:> 0 -该值是最后一次等待的持续时间，单位为百分之一秒-1 -最后一次等待的持续时间小于百分之一秒-2 -参数TIMED_STATISTICS被设置为false该列已不推荐使用，而是支持WAIT_TIME_MICRO和STATE列。
84	SECONDS_IN_WAIT	NUMBER	If the session is currently waiting, then the value is the amount of time waited for the current wait. If the session is not in a wait, then the value is the amount of time since the start of the last wait.This column has been deprecated in favor of the columns WAIT_TIME_MICRO and TIME_SINCE_LAST_WAIT_MICRO. 如果会话当前正在等待，则该值为当前等待的等待时间。如果会话没有处于等待状态，则该值是自上次等待开始以来的时间。该列已被弃用，取而代之的是WAIT_TIME_MICRO和TIME_SINCE_LAST_WAIT_MICRO。
85	STATE	VARCHAR2(19)	Wait state: WAITING - Session is currently waiting WAITED UNKNOWN TIME - Duration of the last wait is unknown; this is the value when the parameter TIMED_STATISTICS is set to false WAITED SHORT TIME - Last wait was less than a hundredth of a second WAITED KNOWN TIME - Duration of the last wait is specified in the WAIT_TIME column 等待状态:WAITING -会话正在等待WAITING - WAITING UNKNOWN TIME -上次等待的持续时间未知;当参数TIMED_STATISTICS设置为false时，该值为waiting SHORT TIME -最后一次等待小于百分之一秒等待的已知时间-最后一次等待的持续时间在WAIT_TIME列中指定
86	WAIT_TIME_MICRO	NUMBER	Amount of time waited (in microseconds). If the session is currently waiting, then the value is the time spent in the current wait. If the session is currently not in a wait, then the value is the amount of time waited in the last wait. 等待的时间数量(以微秒为单位)。如果会话当前正在等待，则该值为当前等待所花费的时间。如果会话当前不在等待中，则该值为上次等待中的等待时间。
87	TIME_REMAINING_MICRO	NUMBER	Value is interpreted as follows: > 0 - Amount of time remaining for the current wait (in microseconds) 0 - Current wait has timed out -1 - Session can indefinitely wait in the current wait NULL - Session is not currently waiting 取值说明:> 0 -当前等待的剩余时间(微秒)0 -当前等待已超时-1 -会话可以在当前等待中无限等待NULL -会话当前没有等待
88	TIME_SINCE_LAST_WAIT_MICRO	NUMBER	Time elapsed since the end of the last wait (in microseconds). If the session is currently in a wait, then the value is 0. 上次等待结束后经过的时间(以微秒为单位)。如果会话当前处于等待状态，则该值为0。
89	SERVICE_NAME	VARCHAR2(64)	Service name of the session 会话的服务名称
90	SQL_TRACE	VARCHAR2(8)	Indicates whether SQL tracing is enabled (ENABLED) or disabled (DISABLED) 指示SQL跟踪是启用(enabled)还是禁用(disabled)
91	SQL_TRACE_WAITS	VARCHAR2(5)	Indicates whether wait tracing is enabled (TRUE) or not (FALSE) 指示是否启用等待跟踪(TRUE)或不启用(FALSE)
92	SQL_TRACE_BINDS	VARCHAR2(5)	Indicates whether bind tracing is enabled (TRUE) or not (FALSE) 指示是否启用绑定跟踪(TRUE)或不启用(FALSE)
93	SQL_TRACE_PLAN_STATS	VARCHAR2(10)	Frequency at which row source statistics are dumped in the trace files for each cursor: never first_execution all_executions 在每个游标的跟踪文件中转储行源统计信息的频率:从不first_execution all_executions
94	SESSION_EDITION_ID	NUMBER	Shows the value that, in the session, would be reported by sys_context(‘USERENV’, ‘SESSION_EDITION_ID’) 显示sys_context(‘USERENV’， ‘SESSION_EDITION_ID’)将在会话中报告的值。
95	CREATOR_ADDR	RAW(4 | 8)	Address of the creating process or circuit 对应V$PROCESS的ADDR列，若当前会话为KILLED状态的话，则可以使用该列来返回会话的地址。
96	CREATOR_SERIAL#	NUMBER	Serial number of the creating process or circuit 创建过程或电路的序列号
97	ECID	VARCHAR2(64)	Execution context identifier (sent by Application Server) 执行上下文标识符(由Application Server发送)
98	SQL_TRANSLATION_PROFILE_ID	NUMBER	Object number of the SQL translation profile SQL翻译概要文件的对象号
99	PGA_TUNABLE_MEM	NUMBER	The amount of tunable PGA memory (in bytes). Untunable memory is PGA_ALLOC_MEM from V$PROCESSminusPG{A}_{T}UNABL{E}_{M}EMfromV$SESSION. 可调PGA内存量(以字节为单位)。不可调内存是V$PROCESS中的PG{A}_{A}LLO{C}_{M}EM减去V$SESSION中的PGA_TUNABLE_MEM。
100	SHARD_DDL_STATUS	VARCHAR2(8)	Indicates whether shard DDL is enabled in the current session (ENABLED) or not (DISABLED). This value is only relevant for the shard catalog database. 指示当前会话中是否启用分片DDL (enabled)或未启用(DISABLED)。此值只与碎片编目数据库相关。
101	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 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
102	EXTERNAL_NAME	VARCHAR2(1024)	External name of the database user. For enterprise users, returns the Oracle Internet Directory DN. 数据库用户的外部名称。对于企业用户，返回Oracle Internet Directory DN。
103		VARCHAR2(5)	Indicates whether the session is connected to a PL/SQL debugger. Possible values: TRUE FALSE 指示会话是否连接到PL/SQL调试器。取值为TRUE和FALSE

1.查看连接oracle的所有机器的并发数（和状态）

# 所有机器的并发数
select machine,count(*) from v$session group by machine;

#所有机器的并发数和z
select machine,status,count(*) from v$session group by machine,status order by status;

2.修改数据库最大连接数

 alter system set processes = 500 scope = spfile;

3.查看Oracle当前有哪些用户正在使用数据

SELECT osuser, a.username,cpu_time/executions/1000000||'s',b.sql_text,machine from v$session a, v$sqlarea b
where a.sql_address =b.address order by cpu_time/executions desc;

4.当前的session连接数

 select count(*) from v$session;

5.获得了当前session的id

如果想获得跟踪文件的名称，而跟踪文件的名称包含了当前session的id，所以获得了当前session的id，也就可以找到当前跟踪文件的名称。

以下3条SQL都可以获取当前session的id

select spid from v$session s, v$process p, v$mystat m where s.paddr=p.addr and s.sid=m.sid and m.statistic#=0;

select spid from v$session s inner join v$process p on s.paddr=p.addr inner join v$mystat m on s.sid=m.sid and m.statistic#=0;

select spid from v$process where addr in (select paddr from v$session where sid=(select sid from v$mystat where rownum=1));