查询使用某个用户连接到数据库的所有客户端

 

1 查询语句如下

==================

select s.username,s.osuser,s.program,s.MACHINE, s.sid, s.serial#, p.spid, 'alter system kill session '||''''||trim(s.sid)||','||trim(s.serial#)||''';'
from v$session s,v$process p where s.paddr = p.addr   and s.username ='修改为所要查询的数据库用户名';

上述查询语句涉及到v$session 和v$process 两个视图，先对其进行简单介绍（来自于网络）

====================

 

 

转载自：http://youyoutianbulao.blog.163.com/blog/static/10193034320108285623253/

V$SESSION

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

V$SESSION中的常用列

V$SESSION是基础信息视图，用于找寻用户SID或SADDR。不过，它也有一些列会动态的变化，可用于检查用户。如例：

SQL_HASH_VALUE，SQL_ADDRESS：这两列用于鉴别默认被session执行的SQL语句。如果为null或0，那就说明这个session没有执行任何SQL语句。PREV_HASH_VALUE和PREV_ADDRESS两列用来鉴别被session执行的上一条语句。

 

注意：当使用SQL*Plus进行选择时，确认你重定义的列宽不小于11以便看到完整的数值。

 

STATUS：这列用来判断session状态是：

l        Achtive：正执行SQL语句(waiting for/using a resource)

l        Inactive：等待操作(即等待需要执行的SQL语句)

l        Killed：被标注为删除

下列各列提供session的信息，可被用于当一个或多个combination未知时找到session。

Session信息

l        SID：SESSION标识，常用于连接其它列

l        SERIAL#：如果某个SID又被其它的session使用的话则此数值自增加(当一个       SESSION结束，另一个SESSION开始并使用了同一个SID)。

l        AUDSID：审查session ID唯一性，确认它通常也用于当寻找并行查询模式

l        USERNAME：当前session在oracle中的用户名。

Client信息

数据库session被一个运行在数据库服务器上或从中间服务器甚至桌面通过SQL*Net连接到数据库的客户端进程启动，下列各列提供这个客户端的信息

l        OSUSER：客户端操作系统用户名

l        MACHINE：客户端执行的机器

l        TERMINAL：客户端运行的终端

l        PROCESS：客户端进程的ID

l        PROGRAM：客户端执行的客户端程序

要显示用户所连接PC的TERMINAL、OSUSER，需在该PC的ORACLE.INI或Windows中设置关键字TERMINAL，USERNAME。

Application信息

调用DBMS_APPLICATION_INFO包以设置一些信息区分用户。这将显示下列各列。

l        CLIENT_INFO：DBMS_APPLICATION_INFO中设置

l        ACTION：DBMS_APPLICATION_INFO中设置

l        MODULE：DBMS_APPLICATION_INFO中设置

下列V$SESSION列同样可能会被用到：

l        ROW_WAIT_OBJ#

l        ROW_WAIT_FILE#

l        ROW_WAIT_BLOCK#

l        ROW_WAIT_ROW#

V$SESSION中的连接列

Column                                                            View                                              Joined Column(s)

SID             V$SESSION_WAIT,,V$SESSTAT,,V$LOCK,V$SESSION_EVENT,V$OPEN_CURSOR                 SID

(SQL_HASH_VALUE, SQL_ADDRESS)                  V$SQLTEXT, V$SQLAREA, V$SQL    (HASH_VALUE, ADDRESS)

(PREV_HASH_VALUE, PREV_SQL_ADDRESS)     V$SQLTEXT, V$SQLAREA, V$SQL    (HASH_VALUE, ADDRESS)

TADDR                                                             V$TRANSACTION                                    ADDR

PADDR                                                              V$PROCESS                                             ADDR

示例：

1.查找你的session信息

SELECTSID, OSUSER, USERNAME, MACHINE, PROCESS

FROMV$SESSIONWHEREaudsid = userenv('SESSIONID');

2.当machine已知的情况下查找session

SELECTSID, OSUSER, USERNAME, MACHINE, TERMINAL

FROMV$SESSION

WHEREterminal ='pts/tl'ANDmachine ='rgmdbs1';

3.查找当前被某个指定session正在运行的sql语句。假设sessionID为100

selectb.sql_text

fromv$session a,v$sqlarea b

wherea.sql_hash_value=b.hash_valueanda.sid=100

寻找被指定session执行的SQL语句是一个公共需求，如果session是瓶颈的主要原因，那根据其当前在执行的语句可以查看session在做些什么。

视图应用：

 

V$session 表的妙用

 

v$session 表中比较常用的几个字段说明^_^

1. sid,serial#
通过sid我们可以查询与这个session相关的各种统计信息,处理信息.
a. select * from v$sesstat where sid = :sid; 
查询用户相关的各种统计信息. 
select a.sid,a.statistic#,b.name,a.value
from v$sesstat a,v$statname b
where a.statistic# = b.statistic#
and a.sid = :sid;

b. 查询用户相关的各种io统计信息
select * from v$sess_io where sid = :sid;

c. 查询用户想在正在打开着的游标变量.
select * from v$open_cursor where sid = :sid;

d. 查询用户当前的等待信息. 以查看当前的语句为什么这么慢/在等待什么资源.
select * from v$session_wait where sid = :sid ;

e. 查询用户在一段时间内所等待的各种事件的信息. 以了解这个session所遇到的瓶颈^_^
select * from v$session_event where sid = :sid;

f. 还有, 就是当我们想kill当前session的时候可以通过sid,serial#来处理.
alter system kill session ':sid,:serail#';

2. paddr.字段, process addr, 通过这个字段我们可以查看当前进程的相关信息, 系统进程id,操作系统用户信息等等.
select a.pid,a.spid,b.name,b.description,a.latchwait,a.latchspin,a.pga_used_mem,a.pga_alloc_mem,a.pga_freeable_mem,a.pga_max_mem
from v$process a,v$bgprocess b
where a.addr = b.paddr(+)
and a.addr = :paddr

3. command 字段, 表明当前session正在执行的语句的类型.请参考reference.

4. taddr 当前事务的地址,可以通过这个字段查看当前session正在执行的事务信息, 使用的回滚段信息等^_^
select b.name rollname,a.*
from v$transaction a,v$rollname b
where a.xidusn = b.usn
and a.addr = '585EC18C';

5. lockwait字段, 可以通过这个字段查询出当前正在等待的锁的相关信息.
select *
from v$lock
where (id1,id2) = (
select id1,id2 from v$lock where kaddr = '57C68C48'
)

6. (sql_address,sql_hash_value) (prev_sql_addr,prev_hash_value) 根据这两组字段, 我们可以查询到当前session正在执行的sql语句的详细信息.

select * from v$sqltext where address = :sql_address and hash_value = :sql_hash_value;

7.ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#
可以通过这几个字段查询现在正在被锁的表的相关信息.^_^
a. 首先得到被锁的的信息
select * from dba_objects where object_id = :row_wait_obj#;
b. 根据row_wait_file#可以找出对应的文件的信息.
select * from v$datafile where file# = :row_wait_file#.
c. 在根据以上四个字段构造出被锁的字段的rowid信息.
select dbms_rowid.ROWID_CREATE(1,:row_wait_obj#,:row_wait_file#,:row_wait_block#,:row_wait_row#) from dual;

8. logon_time 当前session的登录时间.
9. last_call_et 该session idle的时间, 每3秒中更新一次^_^

 

=====================

转载自：http://hougbin.iteye.com/blog/847033

v$process 视图

   作用： v$process视图包含当前系统oracle运行的所有进程信息。常被用于将oracle或服务进程的操作系统进程ID与数据库session之间建立联系。

    表结构

  SQL> desc v$process;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 ADDR                                               RAW(4)
 PID                                                NUMBER
 SPID                                               VARCHAR2(12)
 USERNAME                                           VARCHAR2(15)
 SERIAL#                                            NUMBER
 TERMINAL                                           VARCHAR2(16)
 PROGRAM                                            VARCHAR2(64)
 TRACEID                                            VARCHAR2(255)
 BACKGROUND                                         VARCHAR2(1)
 LATCHWAIT                                          VARCHAR2(8)
 LATCHSPIN                                          VARCHAR2(8)
 PGA_USED_MEM                                       NUMBER
 PGA_ALLOC_MEM                                      NUMBER
 PGA_FREEABLE_MEM                                   NUMBER
 PGA_MAX_MEM                                        NUMBER

 

如果数据库瓶颈是系统资源(如：cpu,内存)，并且占用资源最多的用户总是停留在某几个服务进程，那么进行如下诸项：
找出资源进程；
找出它们的session，你必须将进程与会话联系起来；
找出为什么session占用了如此多的资源。

SQL跟踪文件名是基于服务进程的操作系统进程ID。要找出session的跟踪文件，你必须将session与服务进程联系起来。
某些事件，如rdbms ipc reply，鉴别session进程的Oracle进程ID在等什么。要发现这些进程在做什么，你必须找出它们的session。
你所看到的服务器上的后台进程(DBWR,LGWR,PMON等)都是服务进程。要想知道他们在做什么，你必须找到他们的session。

V$PROCESS中的常用列
ADDR：进程对象地址
PID：oracle进程ID
SPID：操作系统进程ID

V$PROCESS中的连接列
Column            View                         Joined Column(s)
ADDR              V$SESSION               PADDR

示例：

查找指定系统用户在oracle中的session信息及进程id，假设操作系统用户为：junsansi
select s.sid,s.SERIAL#, s.username,p.spid
from v$session s, v$process p
where s.osuser = 'junsansi'
   and s.PADDR = p.ADDR

查看锁和等待：
SELECT /*+ rule */
lpad(' ', decode(l.xidusn, 0, 3, 0)) || l.oracle_username User_name,
o.owner,o.object_name,o.object_type,s.sid,s.serial#,p.spid
FROM v$locked_object l, dba_objects o, v$session s, v$process p
WHERE l.object_id = o.object_id
   AND l.session_id = s.sid and s.paddr = p.addr
ORDER BY o.object_id, xidusn DESC

附注：

在linux环境可以通过ps查看进程信息包括pid，windows中任务管理器的PID与v$process中pid不能一一对应，这块在oracleDocument中也没有找到介绍，后来google了一下，有资料介绍说是由于windows是多线程服务器，每个进程包含一系列线程。这点于unix等不同，Unix每个Oralce进程独立存在，在Nt上所有线程由Oralce进程衍生。
要在windows中显示oracle相关进程pid，我们可以通过一个简单的sql语句来实现。
SELECT s.SID, p.pid, p.spid signaled, s.osuser, s.program
FROM v$process p, v$session s
WHERE p.addr = s.paddr;

还可以通过和 v$bgprocess 连接查询到后台进程的名字：
SELECT s.SID SID, p.spid threadid, p.program processname, bg.NAME NAME
FROM v$process p, v$session s, v$bgprocess bg
WHERE p.addr = s.paddr
   AND p.addr = bg.paddr
   AND bg.paddr <> '00';

 

Eygle大师写了一段sql脚本getsql.sql，用来获取指定pid正在执行的sql语句，在此也附注上来。

REM getsql.sql
REM author eygle
REM 在windows上,已知进程ID,得到当前正在执行的语句
REM 在windows上,进程ID为16进制,需要转换,在UNIX直接为10进制
SELECT   /*+ ORDERED */
         sql_text
    FROM v$sqltext a
   WHERE (a.hash_value, a.address) IN (
            SELECT DECODE (sql_hash_value,
                           0, prev_hash_value,
                           sql_hash_value
                          ),
                   DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
              FROM v$session b
             WHERE b.paddr = (SELECT addr
                                FROM v$process c
                               WHERE c.spid = TO_NUMBER ('&pid', 'xxxx')))
ORDER BY piece ASC
/