oracle 常用视图之-----v$session
作用：每一个连接到Oracle数据库的会话都能在该视图中对应一条记录，根据该视图中的信息可以查询该会话使用的用户，正在执行或者刚刚执行的SQL语句，连接者的信息等。
?
比较有用的字段：
????
SID：会话的标识，具有唯一性，通常要对某个会话进行分析前，首先就需要获得该会话的SID。
SERIAL#：会话的序号。
SADDR：会话所属进程的地址，关联V$PROCESS视图即可查到该会话的所属进程，然后再通过V$PROCESS视图得到对应的操作系统进程号（Windows对应的是线程号）。
USERNAME：创建该会话的用户名。
CLIENT_INFO：还记得SET COMMAND ID命令吗？该命令设置的值就会在V$SESSION.CLIENT_INFO中体现。
OSUSER：客户端操作系统的用户名。
MACHINE：客户端的机器名。
TERMINAL：客户端运行的终端名。
PROGRAM：客户端执行的程序名。
SQL_ADDRESS：执行SQL的地址。
SQL_HASH_VALUE：执行SQL的HASH值，与SQL_ADDRESS关联查询其他SQL相关视图后即可查询会话当前正在执行的SQL语句。
?EVENT：当前会话的等待事件。
??USER#? 使用的oracle 用户名对应的id
?? LOCKWAIT? 锁的状态
?? STATUS 会话状态 ACTIVE , INACTIVE, KILLED, CACHED , SNIPED?
结构：
SQL> desc v$session;
?名称????????????????????????????????????? 是否为空? 类型
?----------------------------------------- -------- ----------------------
?SADDR????????????????????????????????????????????? RAW(4)
?SID??????????????????????????????????????????????? NUMBER
?SERIAL#??????????????????????????????????????????? NUMBER
?AUDSID???????????????????????????????????????????? NUMBER
?PADDR????????????????????????????????????????????? RAW(4)
?USER#????????????????????????????????????????????? NUMBER
?USERNAME?????????????????????????????????????????? VARCHAR2(30)
?COMMAND??????????????????????????????????????????? NUMBER
?OWNERID??????????????????????????????????????????? NUMBER
?TADDR????????????????????????????????????????????? VARCHAR2(8)
?LOCKWAIT?????????????????????????????????????????? VARCHAR2(8)
?STATUS???????????????????????????????????????????? VARCHAR2(8)
?SERVER???????????????????????????????????????????? VARCHAR2(9)
?SCHEMA#??????????????????????????????????????????? NUMBER
?SCHEMANAME???????????????????????????????????????? VARCHAR2(30)
?OSUSER???????????????????????????????????????????? VARCHAR2(30)
?PROCESS??????????????????????????????????????????? VARCHAR2(12)
?MACHINE??????????????????????????????????????????? VARCHAR2(64)
?TERMINAL?????????????????????????????????????????? VARCHAR2(16)
?PROGRAM??????????????????????????????????????????? VARCHAR2(64)
?TYPE?????????????????????????????????????????????? VARCHAR2(10)
?SQL_ADDRESS??????????????????????????????????????? RAW(4)
?SQL_HASH_VALUE???????????????????????????????????? NUMBER
?PREV_SQL_ADDR????????????????????????????????????? RAW(4)
?PREV_HASH_VALUE??????????????????????????????????? NUMBER
?MODULE???????????????????????????????????????????? VARCHAR2(48)
?MODULE_HASH??????????????????????????????????????? NUMBER
?ACTION???????????????????????????????????????????? VARCHAR2(32)
?ACTION_HASH??????????????????????????????????????? NUMBER
?CLIENT_INFO??????????????????????????????????????? VARCHAR2(64)
?FIXED_TABLE_SEQUENCE?????????????????????????????? NUMBER
?ROW_WAIT_OBJ#????????????????????????????????????? NUMBER
?ROW_WAIT_FILE#???????????????????????????????????? NUMBER
?ROW_WAIT_BLOCK#??????????????????????????????????? NUMBER
?ROW_WAIT_ROW#????????????????????????????????????? NUMBER
?LOGON_TIME???????????????????????????????????????? DATE
?LAST_CALL_ET?????????????????????????????????????? NUMBER
?PDML_ENABLED?????????????????????????????????????? VARCHAR2(3)
?FAILOVER_TYPE????????????????????????????????????? VARCHAR2(13)
?FAILOVER_METHOD??????????????????????????????????? VARCHAR2(10)
?FAILED_OVER??????????????????????????????????????? VARCHAR2(3)
?RESOURCE_CONSUMER_GROUP??????????????????????????? VARCHAR2(32)
?PDML_STATUS??????????????????????????????????????? VARCHAR2(8)
?PDDL_STATUS??????????????????????????????????????? VARCHAR2(8)
?PQ_STATUS????????????????????????????????????????? VARCHAR2(8)
?CURRENT_QUEUE_DURATION???????????????????????????? NUMBER
?CLIENT_IDENTIFIER????????????????????????????????? VARCHAR2(64)
?
每个字段的含义：
v$session表每个字段的意义：
$SESSION
This view lists session information for each current session.
Column?? Datatype?? Description??
SADDR?
RAW(4)?
Session address?
SID?
NUMBER?
Session identifier?
SERIAL#?
NUMBER?
Session serial number. Used to identify uniquely 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?
AUDSID?
NUMBER?
Auditing session ID?
PADDR?
RAW(4)?
Address of the process that owns this session?
USER#?
NUMBER?
Oracle user identifier?
USERNAME?
VARCHAR2(30)?
Oracle username?
COMMAND?
NUMBER?
Command in progress (last statement parsed); for a list of values, see Table 3-7?
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 4Byte value. The low-order 2Bytes of which represent the session number, and the high-order bytes the instance ID of the query coordinator?
TADDR?
VARCHAR2(8)?
Address of transaction state object?
LOCKWAIT?
VARCHAR2(8)?
Address of lock waiting for; NULL if none?
STATUS?
VARCHAR2(8)?
Status of the session: ACTIVE (currently executing SQL), INACTIVE, KILLED (marked to be killed), CACHED (temporarily cached for use by Oracle*XA), SNIPED (session inactive, waiting on the client)?
SERVER?
VARCHAR2(9)?
Server type: DEDICATED, SHARED, PSEUDO, NONE?
SCHEMA#?
NUMBER?
Schema user identifier?
SCHEMANAME?
VARCHAR2(30)?
Schema user name?
OSUSER?
VARCHAR2(15)?
Operating system client user name?
PROCESS?
VARCHAR2(9)?
Operating system client process ID?
MACHINE?
VARCHAR2(64)?
Operating system machine name?
TERMINAL?
VARCHAR2(10)?
Operating system terminal name?
PROGRAM?
VARCHAR2(48)?
Operating system program name?
TYPE?
VARCHAR2(10)?
Session type?
SQL_ADDRESS?
RAW(4)?
Used with SQL_HASH_VALUE to identify the SQL statement that is currently being executed?
SQL_HASH
_VALUE?
NUMBER?
Used with SQL_ADDRESS to identify the SQL statement that is currently being executed?
MODULE?
VARCHAR2(48)?
Contains the name of the currently executing module as set by calling the DBMS_APPLICATION_INFO.SET_MODULE procedure?
MODULE_HASH?
NUMBER?
The hash value of the above MODULE?
ACTION?
VARCHAR2(32)?
Contains the name of the currently executing action as set by calling the
DBMS_APPLICATION_INFO.SET_ACTION procedure?
ACTION_HASH?
NUMBER?
The hash value of the above action name?
CLIENT_INFO?
VARCHAR2(64)?
Information set by the DBMS_APPLICATION_INFO.SET_CLIENT_INFO procedure?
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?
ROW_WAIT_OBJ#?
NUMBER?
Object ID for the table containing the ROWID specified in ROW_WAIT_ROW#?
ROW_WAIT_FILE#?
NUMBER?
Identifier for the datafile containing the ROWID 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
_BLOCK#?
NUMBER?
Identifier for the block containing the ROWID 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?
The current ROWID 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?
LOGON_TIME?
DATE?
Time of logon?
LAST_CALL_ET?
NUMBER?
The last call?
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.?
PDML_ENABLED?
VARCHAR2(3)?
This column has been replaced by PDML_ENABLED and PDML_STATUS. See above.?
FAILOVER_TYPE?
VARCHAR2(10)?
NONE if failover is disabled for this session, SESSION if client is able to failover its session following a disconnect, and SELECT if client is able to fail over selects in progress as well?
FAILOVER
_METHOD?
VARCHAR2(3)?
NONE if failover is disabled for this session, BASIC if client reconnects following a disconnect, PRECONNECT if the backup instance is able to support all connections from every instance that it is backup for?
FAILED_OVER?
VARCHAR2(13)?
TRUE if running in failover mode and have failed over, otherwise FALSE?
RESOURCE_CONSUMER_GROUP?
VARCHAR2(32)?
常用的命令：
查询每台应用服务器占用oracle数据库会话情况
select machine, count(*) c from v$session group by machine order by c desc;
?
根据机器名查询该机器发出的所有会话
select t.STATE,t.* from v$session? t where t.MACHINE like '%computername%'
?
查询当前数据库连接会话中正在等待资源或者事件的信息
select * from v$session_wait
?
通过sid或者session对应的完整sql
select sql_text from v$sqltext a where a.hash_value = (select sql_hash_value from v$session b where b.sid = '&sid'??? )
order by piece asc
?
#查看某个表的索引
select index_name,index_type from user_indexes where table_name = '&tableName'
?
?
中断回话
ALTER SYSTEM KILL SESSION 'SID,SERIAL#'；
?
