oracle中session跟process的研究

首先看看v$session跟v$process中主要的欄位屬性:

v$session(sid,serial#,paddr,username,status,machine,terminal,sql_hash_value,sql_address,,,)

v$process(addr,spid,,,)

可看到v$session中的paddr跟v$process中的addr對應,也即會話session在資料庫主機上對應程式的程式地址.

這裡我們要先定位該session正在執行的sql語句,此時我們可以查詢如下的語句:
select sql_text
from v$sqltext_with_newlines
where (hash_value,address) in (select sql_hash_value,sql_address from v$session where sid=&sid) order by address,piece;

若需手工kill一個session,可採用"alter system kill session 'sid,serial#' immediate",此時session的狀態會標記為killed,該session對應程式地址指向相同的虛擬地址,見如下的查詢:

SQL> select sid,serial#,paddr,status from v$session where username='SHIYIHAI';

SID SERIAL# PADDR STATUS
---------- ---------- ---------------- --------
234 14409 C0000000B2BADB28 INACTIVE
245 14374 C0000000B2BAB748 INACTIVE

SQL> alter system kill session '234,14409' immediate;

System altered

SQL> select sid,serial#,paddr,status from v$session where username='SHIYIHAI';

SID SERIAL# PADDR STATUS
---------- ---------- ---------------- --------
234 14409 C0000000B2C750B8 KILLED
245 14374 C0000000B2BAB748 INACTIVE

SQL> alter system kill session '245,14374' immediate;

System altered

SQL> select sid,serial#,paddr,status from v$session where username='SHIYIHAI';

SID SERIAL# PADDR STATUS
---------- ---------- ---------------- --------
234 14409 C0000000B2C750B8 KILLED
245 14374 C0000000B2C750B8 KILLED

SQL>

我們知道很多時候被kill掉的session並不能馬上釋放資源,我們需要從os上kill掉對應的程式,但由於session的paddr被修改為虛擬地址了,導致無法關聯v$process,找不到程式的spid程式號.此時我們可以執行如下查詢:

SQL> SELECT s.username,s.status,
2 x.ADDR,x.KSLLAPSC,x.KSLLAPSN,x.KSLLASPO,x.KSLLID1R,x.KSLLRTYP,
3 decode(bitand (x.ksuprflg,2),0,null,1)
4 FROM x$ksupr x,v$session s
5 WHERE s.paddr(+)=x.addr
6 and bitand(ksspaflg,1)!=0
7 and s.status is null;

USERNAME STATUS ADDR KSLLAPSC KSLLAPSN KSLLASPO KSLLID1R KSLLRTYP DECODE(BITAND(X.KSUPRFLG,2),0,
------------------------------ -------- ---------------- ---------- ---------- ------------ ---------- -------- ------------------------------
C0000000B2B58D08 0 0 0
C0000000B2BA4688 298 9 4669 0
C0000000B2BAB748 1 16 4675 197 EV
C0000000B2BADB28 1 16 4675 197 EV

SQL>

或查詢如下的語句:

SQL> select p.addr from v$process p where pid <> 1
2 minus
3 select s.paddr from v$session s;

ADDR
----------------
C0000000B2BA4688
C0000000B2BAB748
C0000000B2BADB28

SQL>

從查詢結果中可發現被kill掉的session對應的程式地址.然後透過程式地址來查詢對應的程式號,見如下的查詢:

SQL> select spid from v$process where addr in ('C0000000B2BAB748','C0000000B2BADB28');

SPID
------------
13013
13015

最後在作業系統上執行"kill -9 13013"和"kill -9 13015"即可.

[@more@]