杀掉oracle进程的方法

sql 代码

----杀掉oracle进程的方法----

1.通常可以使用以下命令Kill进程:
alter system kill session 'sid,serial#';
2.
select logon_time,username,action,program,terminal,machine,userfrom v$session where terminal = 'CZYS'
select spid from v$process where addr in
 (select paddr from v$session where terminal ='CZYS'
   andlogon_time <= to_date('2006-2-17 16:28:56','yyyy-mm-ddhh24:mi:ss'));


-------------------------------
为了更快速的释放资源，通常我们使用如下步骤来Kill进程:
1.首先在操作系统级kill进程
2.在数据库内部kill session
这样通常可以快速中止进程，释放资源。

首先查询得到该session对应的OS进程号:
SQL> select 'kill -9 '||spid from v$process where addr = (selectpaddr from v$session where sid=&sid);
Enter value for sid: 154
old   1: select 'kill -9 '||spidfrom v$process where addr = (select paddr from v$session wheresid=&sid)
new   1: select 'kill -9 '||spidfrom v$process where addr = (select paddr from v$session wheresid=154)

'KILL-9'||SPID
--------------------
kill -9 22702

SQL> !
在操作系统级kill该进程:
[oracle@danaly ~]$ ps -ef|grep 22702
oracle  22702    1  0 Oct25?       00:00:02 oracledanaly (LOCAL=NO)
oracle   1208212063  0 11:12pts/1   00:00:00 grep 22702
[oracle@danaly ~]$ kill -9 22702
[oracle@danaly ~]$ ps -ef|grep 22702
oracle   1208812063  0 11:12pts/1   00:00:00 grep 22702
[oracle@danaly ~]$ exit
exit

SQL> select sid,username,status from v$session;

      SIDUSERNAME                      STATUS
---------- ------------------------------ --------
...
      154SCOTT                         KILLED
...

30 rows selected.

SQL> select sid,serial#,username from v$session wheresid=154;

      SID    SERIAL#USERNAME
---------- ---------- ------------------------------
      154     56090 SCOTT

再次在数据库中kill该session,并指定immediate选项:
SQL> alter system kill session '154,56090' immediate;

System altered.

SQL> select sid,serial#,username from v$session wheresid=154;

no rows selected


此时该进程被迅速清除。

--------------------------------------------------------
查找锁定的session
SELECT   sn.username,m.SID,sn.SERIAL#, m.TYPE,
        DECODE (m.lmode,
                0, 'None',
                1, 'Null',
                2, 'Row Share',
                3, 'Row Excl.',
                4, 'Share',
                5, 'S/Row Excl.',
                6, 'Exclusive',
                lmode, LTRIM (TO_CHAR (lmode, '990'))
               ) lmode,
        DECODE (m.request,
                0, 'None',
                1, 'Null',
                2, 'Row Share',
                3, 'Row Excl.',
                4, 'Share',
                5, 'S/Row Excl.',
                6, 'Exclusive',
                request, LTRIM (TO_CHAR (m.request, '990'))
               ) request,
        m.id1, m.id2
    FROMv$session sn, v$lock m
   WHERE (sn.SID = m.SID ANDm.request !=0)        --存在锁请求，即被阻塞
     OR (    sn.SID =m.SID                        --不存在锁请求，但是锁定的对象被其他会话请求锁定
         AND m.request = 0
         AND lmode != 4
         AND (id1, id2) IN (
                       SELECT s.id1, s.id2
                         FROM v$lock s
                        WHERE request != 0 AND s.id1 = m.id1
                              AND s.id2 = m.id2)
        )
ORDER BY id1, id2, m.request;
--------------------------------------------------------------
通过数据字典V$SESSION与V$LOCK来了解正在等待锁资源的用户
select a.username,a.sid,a.serial#,b.id1 from v$session a ,v$lockb
        Where a.lockwait = b.kaddr;
了解锁住其他用户的用户进程，方法如下：
select a.username,a.sid,a.serial#,b.id1 from v$session a,v$lockb
        Where b.id1 in
                (select distinct e.id1 from v$session d,v$lock e where d.lockwait =e.kaddr)
                And a.sid = b.sid and b.request =0;

分享到：

200802工作日志 | spool命令使用(不显示select命令的方法)

• 2007-10-23 21:04
• 浏览 11988
• 评论(0)
• 查看更多