Oracle常用视图----v$session
作用：每一个连接到Oracle数据库的会话都能在该视图中对应一条记录，根据该视图中的信息可以查询该会话使用的用户，正在执行或者刚刚执行的SQL语句，连接者的信息等。比较有用的字段：
SID：会话的标识，具有唯一性，通常要对某个会话进行分析前，首先就需要获得该会话的SID。
SERIAL#：会话的序号。
SADDR：会话所属进程的地址，关联V$PROCESS视图即可查到该会话的所属进程，然后再通过V$PROCESS视图得到对应的操作系统进程号（Windows对应的是线程号）。
USERNAME：创建该会话的用户名。
CLIENT_INFO：还记得SET COMMAND> 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> SERIAL#
NUMBER
Sessionserial number. Used to> AUDSID
NUMBER
Auditing session> PADDR
RAW(4)
Address of the process that owns this session
USER#
NUMBER
Oracle user> USERNAME
VARCHAR2(30)
Oracle username
COMMAND
NUMBER
Command in progress (last statement parsed); for a list of values, see Table 3-7
OWNERID
NUMBER
Thecolumn contents are invalid if the value is 2147483644. Otherwise, thiscolumn contains the>
For operations using Parallel Slaves, interpret thisvalue as a 4Byte value. The low-order 2Bytes of which represent thesession number, and the high-order bytes the instance> TADDR
VARCHAR2(8)
Address of transaction state object
LOCKWAIT
VARCHAR2(8)
Address of lock waiting for; NULL if none
STATUS
VARCHAR2(8)
Statusof 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> SCHEMANAME
VARCHAR2(30)
Schema user name
OSUSER
VARCHAR2(15)
Operating system client user name
PROCESS
VARCHAR2(9)
Operating system client process> 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> SQL_HASH
_VALUE
NUMBER
Used with SQL_ADDRESS to> 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
Thiscontains a number that increases every time the session completes acall to the database and there has been an intervening select from adynamic performance table. This column can be used by performancemonitors to monitor statistics in the database. Each time theperformance monitor looks at the database, it only needs to look atsessions that are currently active or have a higher value in this columnthan the highest value that the performance monitor saw the last time.All the other sessions have been> ROW_WAIT_OBJ#
NUMBER
Object> ROW_WAIT_FILE#
NUMBER
Identifierfor the datafile containing the ROWID specified in ROW_WAIT_ROW#. Thiscolumn is valid only if the session is currently waiting for anothertransaction to commit and the value of ROW_WAIT_OBJ# is not -1
ROW_WAIT
_BLOCK#
NUMBER
Identifierfor the block containing the ROWID specified in ROW_WAIT_ROW#. Thiscolumn is valid only if the session is currently waiting for anothertransaction to commit and the value of ROW_WAIT_OBJ# is not -1
ROW_WAIT_ROW#
NUMBER
Thecurrent ROWID being locked. This column is valid only if the session iscurrently waiting for another transaction to commit and the value ofROW_WAIT_OBJ# is not -1
LOGON_TIME
DATE
Time of logon
LAST_CALL_ET
NUMBER
The last call
PDML_STATUS
VARCHAR2(8)
IfENABLED, 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> PDDL_STATUS
VARCHAR2(8)
IfENABLED, 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> PDML_ENABLED
VARCHAR2(3)
This column has been replaced by PDML_ENABLED and PDML_STATUS. See above.
FAILOVER_TYPE
VARCHAR2(10)
NONEif failover is disabled for this session, SESSION if client is able tofailover its session following a disconnect, and SELECT if client isable to fail over selects in progress as well
FAILOVER
_METHOD
VARCHAR2(3)
NONEif failover is disabled for this session, BASIC if client reconnectsfollowing a disconnect, PRECONNECT if the backup instance is able tosupport 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$sessiont 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#'；
页:
[1]