session1 确认sid
SYS @ prod > select userenv('sid') from dual;
USERENV('SID')
--------------
144
session2 确认sid
SYS @ prod > select userenv('sid') from dual;
USERENV('SID')
--------------
145
session1 查询当前数据库有哪些连接
SYS @ prod > select p.spid,p.pid,p.username,ss.sid,ss.serial# from v$process p,v$session ss where p.addr=ss.paddr and ss.username is not null;
SPID PID USERNAME SID SERIAL#
------------ ---------- --------------- ---------- ----------
12244 18 oracle 144 279
12269 19 oracle 145 100
操作系统层面查看以上两个进程
[root@ora10g ~]# ps -ef |grep 12244
oracle 12244 12243 0 18:32 ? 00:00:00 oracleprod (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
root 12298 12272 0 18:34 pts/4 00:00:00 grep 12244
[root@ora10g ~]# ps -ef |grep 12269
oracle 12269 12268 0 18:32 ? 00:00:00 oracleprod (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
root 12300 12272 0 18:34 pts/4 00:00:00 grep 12269
登陆session1 kill session2
SYS @ prod > alter system kill session '145,100';
System altered.
再次查询2个会话的状态，可以发现 session2 仍然存在，但是 session status 变为 killed
SYS @ prod > select p.spid,p.pid,p.username,ss.sid,ss.serial#,ss.status from v$process p,v$session ss where p.addr(+)=ss.paddr and ss.username is not null;
SPID PID USERNAME SID SERIAL# STATUS
------------ ---------- --------------- ---------- ---------- --------
12244 18 oracle 144 279 ACTIVE
145 100 KILLED
操作系统层面查看进程状态
[root@ora10g ~]# ps -ef |grep 12244
oracle 12244 12243 0 18:32 ? 00:00:00 oracleprod (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
root 12372 12272 0 19:00 pts/4 00:00:00 grep 12244
[root@ora10g ~]# ps -ef |grep 12269
oracle 12269 12268 0 18:32 ? 00:00:00 oracleprod (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
root 12379 12272 0 19:01 pts/4 00:00:00 grep 12269
查询该进程的详细信息
SYS @ prod > select p.spid,p.pid,p.username,ss.sid,ss.serial# from v$process p,v$session ss where p.spid=12269;
SPID PID USERNAME SID SERIAL#
------------ ---------- --------------- ---------- ----------
12269 19 oracle 143 159
12269 19 oracle 144 279
12269 19 oracle 145 100
12269 19 oracle 155 1
12269 19 oracle 156 1
12269 19 oracle 160 1
12269 19 oracle 161 1
12269 19 oracle 162 1
12269 19 oracle 163 1
12269 19 oracle 164 1
12269 19 oracle 165 1
12269 19 oracle 166 1
12269 19 oracle 167 1
12269 19 oracle 168 1
12269 19 oracle 169 1
12269 19 oracle 170 1
kill session
SYS @ prod > alter system kill session '145,100';
System altered.
SYS @ prod > select p.spid,p.pid,p.username,ss.sid,ss.serial# from v$process p,v$session ss where p.spid=12269;
SPID PID USERNAME SID SERIAL#
------------ ---------- --------------- ---------- ----------
12269 19 oracle 143 174
12269 19 oracle 144 279
12269 19 oracle 155 1
12269 19 oracle 156 1
12269 19 oracle 160 1
12269 19 oracle 161 1
12269 19 oracle 162 1
12269 19 oracle 163 1
12269 19 oracle 164 1
12269 19 oracle 165 1
12269 19 oracle 166 1
12269 19 oracle 167 1
12269 19 oracle 168 1
12269 19 oracle 169 1
12269 19 oracle 170 1
操作系统层面杀掉 进程
[root@ora10g ~]# kill -9 12269
结论：先查询session 对应的 spid，然后从操作系统层面kill spid 才可以真正的杀掉进程
以下为 eygle的帖子，做参考
http://www.eygle.com/faq/Kill_Session.htm