client、server process以及session的对应关系

先介绍几张常用的视图：

###########V$SESSION#############

V$SESSION displays session information for each current session.

下面是一些常用的列。

SID ： Session identifier

SERIAL# ： Session serial number

AUDSID ： Auditing session ID

PADDR ： Address of the server process that owns the session

USER# : Oracle user identifier

USERNAME : Oracle username

LOCKWAIT : Address of the lock the session is waiting for; NULL if none

STATUS : Status of the session

SERVER : Server type

SCHEMA# : Schema user identifier

SCHEMANAME : Schema user name

OSUSER : Operating system client user name

PROCESS : Operating system client process ID

MACHINE : Operating system machine name

PORT : Client port number

PROGRAM : Operating system program name

TYPE : Session type,分为BACKGROUND和USER

SQL_ADDRESS ： Used with SQL_HASH_VALUE to identify the SQL statement that is currently being executed

SQL_HASH_VALUE ： Used with SQL_ADDRESS to identify the SQL statement that is currently being executed

ROW_WAIT_OBJ# ： Object ID for the table containing the row specified in ROW_WAIT_ROW#

ROW_WAIT_FILE# ： 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 ofROW_WAIT_OBJ# is not-1.

ROW_WAIT_BLOCK# ： 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 ofROW_WAIT_OBJ# is not-1.

ROW_WAIT_ROW# ： Current row being locked. This column is valid only if the session is currently waiting for another transaction to commit and the value ofROW_WAIT_OBJ# is not-1.

LOGON_TIME ： Time of logon

###########V$PROCESS#############

V$PROCESS displays information about the currently active processes.

注意这里的processes指的是服务器端的进程(后台进程和server process)

下面是一些常用的列。

ADDR : Address of the process state object

PID ： Oracle process identifier

SPID ： Operating system process identifier

USERNAME ： Operating system process username

PROGRAM ： Program in progress

BACKGROUND ： 1 for a background process; NULL for a normal process

###########V$SQLTEXT#############

V$SQLTEXT displays the text of SQL statements belonging to shared SQL cursors in the SGA.

下面是一些常用的列。

ADDRESS ： Used with HASH_VALUE to uniquely identify a cached cursor

HASH_VALUE ： Used with ADDRESS to uniquely identify a cached cursor

SQL_ID ： SQL identifier of a cached cursor

PIECE ： Number used to order the pieces of SQL text

SQL_TEXT ： A column containing one piece of the SQL text

 

有了上面的基础之后，我们就可以获得client、server process以及相应session的对应关系。

比如：用户A通过客户端sqlplus连上远程数据库，那如何定位oracle服务器那边是哪个server process为该用户服务？以及该server process对应的session信息?

  以及用户A正在执行哪条sql语句？

（1）获取数据库所有会话信息

select t.SID "会话id",t.LOGON_TIME "登录时间",t.TYPE "会话类型",t.USERNAME "数据库用户",t.STATUS "会话状态",t.LOCKWAIT "是否锁等待",t.SERVER "服务器类型",t.OSUSER "客户端用户名",t.PROCESS "客户端进程id",t.MACHINE "客户端机器名",t.PROGRAM "客户端程序"
from v$session t 
where t.USERNAME IS NOT NULL
order by t.LOGON_TIME;

（2）获取session和server process的对应关系

select t1.SID "会话id",t1.TYPE "会话类型",t1.USERNAME "数据库用户",t1.STATUS "会话状态",t1.LOCKWAIT "是否锁等待",t2.SPID "服务器server process",t1.SERVER "服务器类型",t1.OSUSER "客户端用户名",t1.PROCESS "客户端进程id",t1.MACHINE "客户端机器名",t1.PROGRAM "客户端程序"
from v$session t1,v$process t2
where t1.PADDR=t2.ADDR
and t1.USERNAME IS NOT NULL;

（3）获取session正在执行哪条sql语句

select t1.SID "会话id",t1.LOGON_TIME "登录时间",t1.TYPE "会话类型",t2.SQL_TEXT "正在执行的sql语句",t1.USERNAME "数据库用户",t1.STATUS "会话状态",t1.LOCKWAIT "是否锁等待",t1.SERVER "服务器类型",t1.OSUSER "客户端用户名",t1.PROCESS "客户端进程id",t1.MACHINE "客户端机器名",t1.PROGRAM "客户端程序"
 from v$session t1,v$sqltext t2 
where t1.SQL_ADDRESS=t2.ADDRESS
 and t1.SQL_HASH_VALUE=t2.HASH_VALUE
 and t1.USERNAME ='ICD35'
 order by t1.SID,t2.PIECE;

（4）获取session对应的server process以及该session正在执行哪条sql语句

select t1.SID "会话id",t1.LOGON_TIME "登录时间",t1.TYPE "会话类型",t1.USERNAME "数据库用户",t1.STATUS "会话状态",t1.LOCKWAIT "是否锁等待",t3.SPID "服务器server process",t1.SERVER "服务器类型",t1.OSUSER "客户端用户名",t1.PROCESS "客户端进程id",t1.MACHINE "客户端机器名",t1.PROGRAM "客户端程序"
from v$session t1,v$sqltext t2,v$process t3 
where t1.SQL_ADDRESS=t2.ADDRESS
and t1.SQL_HASH_VALUE=t2.HASH_VALUE
and t1.USERNAME IS NOT NULL
and t1.PADDR=t3.ADDR
order by t1.SID,t2.PIECE;

（5）获取当前会话信息

select userenv('sid') from dual;  --SID returns the session ID

select userenv('language') from dual;  --LANGUAGE returns the language and territory used by the current session along with the database character set in this form : language_territory.characterset