Oracle sid，spid，pid

时间：2010-12-03 来源：ank

1>建立两个会话
2>查找登录信息

column machine format a8
select machine,saddr,sid,serial#,paddr,username,status
from v$session where username is not null;

MACHINE SADDR SID SERIAL# PADDR USERNA STATUS
-------- -------- ---- ------- -------- ------ --------

node2 29EF0618 136 132 29E1C370 SYS INACTIVE
node2 29F0440C 153 40 29E1DA40 SYSTEM INACTIVE
node2 29F0B444 159 3 29E1B254 SYS ACTIVE

3>kill session

SQL> alter system kill session '136,132';
System altered.

SQL> alter system kill session '153,40' ;
System altered.

SQL> select machine,saddr,sid,serial#,paddr,username,status
2 from v$session where username is not null;
MACHINE SADDR SID SERIAL# PADDR USERNA STATUS
-------- -------- ---- ------- -------- ------ --------
node2 29EF0618 136 132 29E4C5F0 SYS KILLED
node2 29F0440C 153 40 29E4C5F0 SYSTEM KILLED
node2 29F0B444 159 3 29E1B254 SYS ACTIVE

方式一:此处不能用 SQL> select spid ,addr from v$process where addr in (select p.addr from v$process p where pid <> 1minus select s.paddr from v$session s) order by spid 会话被kill，资源可能未释放，通过sid，查询spid，os级别kill os的进程由于kill session后，paddr已经更改，无法通过v$session和v$process关联来获得spid
方式二：

SQL>
SELECT s.username,s.status,
x.ADDR,x.KSLLAPSC,x.KSLLAPSN,x.KSLLASPO,x.KSLLID1R,x.KSLLRTYP,
decode(bitand (x.ksuprflg,2),0,null,1)
FROM x$ksupr x,v$session s
WHERE s.paddr(+)=x.addr
and bitand(ksspaflg,1)!=0;
USERNA STATUS ADDR KSLLAPSC KSLLAPSN KSLLASPO KSLLID1R KS D
------ -------- -------- ---------- ---------- ------------ ---------- -- -

       ACTIVE 29E22A18 2 4 10084 0 1
       ACTIVE 29E218FC 1 48 10074 0 1
       ACTIVE 29E21EB0 1 48 10076 0 1
       ACTIVE 29E1F6C4 94 4 10084 272 EV 1
       ACTIVE 29E1EB5C 108 4 10101 271 EV 1
       ACTIVE 29E1E5A8 1 48 10074 0 1
       ACTIVE 29E1D48C 49 9 10074 0 1
       ACTIVE 29E1CED8 27 24 10554 0 1
       ACTIVE 29E1C924 23 245 10044 108 EV
       ACTIVE 29E1BDBC 7 25 10038 0 1
       ACTIVE 29E1B808 3 4 10038 0 1

USERNA STATUS ADDR KSLLAPSC KSLLAPSN KSLLASPO KSLLID1R KS D
------ -------- -------- ---------- ---------- ------------ ---------- -- -

SYS ACTIVE 29E1B254 150 15 10024 1194 EV
       ACTIVE 29E1A138 1 48 9496 0 1
       ACTIVE 29E19B84 19 24 10026 0 1
       ACTIVE 29E195D0 51 241 10602 0 1
       ACTIVE 29E1901C 1 48 9496 0 1
       ACTIVE 29E18A68 404 24 10036 0 1
       ACTIVE 29E184B4 39 34 10046 0 1
       ACTIVE 29E17F00 121 4 10602 0 1
       ACTIVE 29E1794C 43 24 10028 0 1
       ACTIVE 29E17398 74 24 10046 0 1
       ACTIVE 29E16DE4 3 25 10062 0 1

USERNA STATUS ADDR KSLLAPSC KSLLAPSN KSLLASPO KSLLID1R KS D
------ -------- -------- ---------- ---------- ------------ ---------- -- -

       ACTIVE 29E16830 1 48 9496 0 1
                29E1A6EC 2 203 10024 0
                29E1627C 0 0 0
                29E1C370 0 0 0
                29E1DA40 0 0 0
                29E1ACA0 1 48 9496 0
28 rows selected.

红色部分为为释放的资源

无用的为：
                29E1A6EC 2 203 10024 0
                29E1627C 0 0 0
                29E1C370 0 0 0
                29E1DA40 0 0 0
                29E1ACA0 1 48 9496 0

知道进程地址，通过v$process视图查找spid 以29E1C370为例子，通过

SQL> select spid from v$process where addr='29E1C370';
SPID
------------
10517
//找到进程
[oracle@node2 ~]$ ps -ef |grep 10517
oracle 10517 10516 0 01:25 ? 00:00:00 oracleTEST (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 10641 10520 0 01:34 pts/4 00:00:00 grep 10517
[oracle@node2 ~]$ kill -9 10517

/////////////////////////////////////////////////////////////////////// 1>得到sid select sid,serial#,machine,username from v$session 2>通过sid查找SQL语句

SQL>select SQL_TEXT from v$sqltext
    where hash_value=
    (select sql_hash_value from v$session
    where sid='&sid')
    order by piece asc;