查看當前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
/
