oracle 授权 v$session,【Oracle】v$session和v$process的使用

查看当前session的sid和serial#:

SYS@ORCL>select sid,serial#,status from v$session where sid=userenv('sid');

SID    SERIAL# STATUS

---------- ---------- --------

89          3 ACTIVE

查看当前session对应的spid：

SYS@ORCL>select spid from v$process p, v$session s where s.sid=userenv('sid') and s.paddr=p.addr;

SPID

------------

18871

查看当前session的trace file的路径：

SYS@ORCL>select p.value||'/'||t.instance||'_ora_'||ltrim(to_char(p.spid,'fm99999'))||'.trc'

2  from v$process p,v$session s,v$parameter p,v$thread t

3  where p.addr = s.paddr and s.audsid = userenv('sessionid') and p.name = 'user_dump_dest';

P.VALUE||'/'||T.INSTANCE||'_ORA_'||LTRIM(TO_CHAR(P.SPID,'FM99999'))||'.TRC'

--------------------------------------------------------------------------------

/u01/app/Oracle/admin/ORCL/udump/ORCL_ora_18871.trc

已知spid，查看当前正在执行或者最近一次执行的SQL

SYS@ORCL>select /*+ordered*/ sql_text

2  from v$sqltext sql

3  where (sql.hash_value,sql.address) in (

4    select decode(sql_hash_value,0,prev_hash_value,sql_hash_value),decode(sql_hash_value,0,prev_sql_addr,sql_address)

5    from v$session s

6    where s.paddr = (select addr from v$process p where p.spid = to_number('&pid')));

Enter value for pid: 18871

old  6:    where s.paddr = (select addr from v$process p where p.spid = to_number('&pid')))

new  6:    where s.paddr = (select addr from v$process p where p.spid = to_number('18871')))

SQL_TEXT

----------------------------------------------------------------

from v$process p where p.spid = to_number('18871')))

sql_address)    from v$session s    where s.paddr = (select addr

ash_value,sql_hash_value),decode(sql_hash_value,0,prev_sql_addr,

value,sql.address) in (    select decode(sql_hash_value,0,prev_h

select /*+ordered*/ sql_text from v$sqltext sql where (sql.hash_

查看锁和等待：

SYS@ORCL>col user_name format a10

SYS@ORCL>col owner format a10

SYS@ORCL>col object_name format a15

SYS@ORCL>col sid format 999999

SYS@ORCL>col serial# format 999999

SYS@ORCL>col spid format a6

SYS@ORCL>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

2  FROM v$locked_object l, dba_objects o, v$session s, v$process p

3  WHERE l.object_id = o.object_id

4    AND l.session_id = s.sid and s.paddr = p.addr

5  ORDER BY o.object_id, xidusn DESC;

no rows selected

查找指定系统用户在oracle中的session信息及进程id，假设操作系统用户为：oracle

SYS@ORCL>select s.sid,s.SERIAL#,s.username,p.spid

2  from v$session s,v$process p

3  where s.oSUSEr='oracle'

4  and s.PADDR=p.ADDR;

Windows环境下

在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

/