【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
/