查询每台应用服务器占用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#';
plsql 命令窗口中用desc v$session可以查看到表结构
比较有用的v$session字段定义:
SADDR Session地址
SID SessionID
SERIAL# Session序列号
USER# 使用的oracle 用户名对应的id
USERNAME 使用的oracle用户名
LOCKWAIT 锁的状态
STATUS 会话状态 ACTIVE , INACTIVE, KILLED, CACHED , SNIPED
OSUSER 用户操作系统的登录名
MACHINE 用户的机器名
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