session1 确认sidSYS @ prod > select userenv('sid') from dual;
USERENV('SID')-------------- 144session2 确认sidSYS @ 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 27912269 19 oracle 145 100
操作系统层面查看以上两个进程[root@ora10g ~]# ps -ef |grep 12244oracle 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 12269oracle 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 session2SYS @ prod > alter system kill session '145,100';
System altered.
再次查询2个会话的状态，可以发现 session2 仍然存在，但是 session status 变为 killedSYS @ 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 12244oracle 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 12269oracle 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 15912269 19 oracle 144 27912269 19 oracle 145 10012269 19 oracle 155 112269 19 oracle 156 112269 19 oracle 160 112269 19 oracle 161 112269 19 oracle 162 112269 19 oracle 163 112269 19 oracle 164 112269 19 oracle 165 112269 19 oracle 166 112269 19 oracle 167 112269 19 oracle 168 112269 19 oracle 169 112269 19 oracle 170 1
kill sessionSYS @ 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 17412269 19 oracle 144 27912269 19 oracle 155 112269 19 oracle 156 112269 19 oracle 160 112269 19 oracle 161 112269 19 oracle 162 112269 19 oracle 163 112269 19 oracle 164 112269 19 oracle 165 112269 19 oracle 166 112269 19 oracle 167 112269 19 oracle 168 112269 19 oracle 169 112269 19 oracle 170 1操作系统层面杀掉 进程[root@ora10g ~]# kill -9 12269
结论：先查询session 对应的 spid，然后从操作系统层面kill spid 才可以真正的杀掉进程
以下为 eygle的帖子，做参考http://www.eygle.com/faq/Kill_Session.htm
转载于:https://www.cnblogs.com/iyoume2008/p/5122105.html
