Windows环境中Kill掉Oracle线程（orakill）

   今天公司碰到一个很倒霉的事，就是Kill的会话迟迟没有释放，导致全公司所有业务部分关联的表停滞，究其原因有很多，但主要原因个人认为：个别用户回话进程长期占用资源而不释放，导致Oracle进程占用了系统的大量资源，Oralce系统的效率变得很低。如果简单的关闭重启Oracle 实例，势必影响所有的用户。 

      Windows是一个基于线程的操作系统，而不是Linux基于进程的操作系统。整个Oracle的后台进程、用户进程等，在Windows 环境下，都包含在ORACLE.EXE这单独的一个体系进程中了，通过查看’任务管理器’――’进程’就可以看到。如果你不是使用MTS多线程服务器的模式，如果你Kill掉ORACLE.EXE这个进程，将导致整个Oracle实例关闭，如同使用Shutdown abort命令一样。

      由于Windows自己没有提过一个专门用来Kill掉单个线程的工具，因此Oracle自己提供了一个基于字符界面的用来在Windows环境下强制Kill掉一个线程的工具――Orakill。

   !!!  在Windows中如果使用alter system kill session 'sid,serial#'来清除会话，在执行之后该会话的状态会变为KILLED，但是有时候这个状态会保持很长时间，直到最后被清除。!!!

   如果想更快地从内存中清理这个会话，那么可以在使用了alter system之后，再在Windows中使用Orakill实用程序（该程序随Oracle数据库同时安装）直接清除该会话的线程。

Orakill的使用方法如下：

Dos提示符下:>orakill sid thread

 说明： sid-----Oracle的sid(SESSION_ID)号

      thread-----Oracle的线程id(spid)号(select pro.spid from v$session ses,v$process pro where ses.sid=SID号 and ses.paddr=pro.addr;)

--查出死锁的详细信息:(包含:SID,SERIAL#,SPID,远端PC,执行的SQL语句,程序名

SELECT s.username,l.SESSION_ID as SID,s.SERIAL#,P.SPID,S.PROGRAM,S.MACHINE ,Q.SQL_FULLTEXT,l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESSFROM V$LOCKED_OBJECT l,V$SESSION S,V$PROCESS P,V$SQL Q WHERE l.SESSION_ID=S.SID AND S.PADDR=P.ADDR ANDQ.HASH_VALUE=S.SQL_HASH_VALUE 

--执行普通Kill

alter system kill session 'SID,SERIAL#'

--杀Linux进程

ps -ef|grep spid

--否则，在WIndows下执行杀Oracle线程:

orakill sid<Oracle实例> spid<前面查询语句的SPID信息>,例如:

set oracle_sid=YMS

orakill YMS 1142  --1142为SPID

还有其他原因需要优化，比如SQL调优。  但今天学到了如何彻底的在Windows下杀死Oracle线程的妙计。 

一些官方指导:

一般情况下，在杀一个会话的时候，直接执行alter system kill session ‘sid,serial#’;

Administrator's Guide说，当session是active的时候，alter system kill session 只是将session标识为killed或者pseudo状态，并不会释放session持有的资源，所以我们在执行完alter system kill session 后，看会话还是一直存在。

这种情况下可以使用 immediate选项，强制立即Kill会话，如下：

SQL> alter system kill session '3964,51752' immediate;

SQL Language Reference

（http://docs.oracle.com/cd/B28359_01/server.111/b28286/toc.htm#BEGIN）

里对Immediate的解释是：IMMEDIATE Specify IMMEDIATE to instruct Oracle

Database to roll back ongoing transactions, release all session locks, recover the entire session state,and return control to you immediately.

另外我们也可以使用alter system disconnect session

The POST_TRANSACTION setting allows ongoing transactions to complete before the session is disconnected.

If the session has no ongoing transactions, then this clause has the same effect described for as KILL SESSION.

The IMMEDIATE setting disconnects the session and recovers the entire session state immediately, without waiting for ongoing transactions to complete.

If you also specify POST_TRANSACTION and the session has ongoing transactions, then the IMMEDIATE keyword is ignored.

If you do not specify POST_TRANSACTION, or you specify POST_TRANSACTION but the session has no ongoing transactions, then this clause has the same effect as described for KILL SESSION IMMEDIATE.

 

ORACLE建议的DCD解决方法

修改sqlnet.ora文件，新增expire_time=x(单位是分钟)

通过ALTER PROFILE DEFAULT LIMIT IDLE_TIME x; 命令修改,重启后生效。

 

通过OS杀进程终止会话

 SELECT spid, osuser, s.program, schemaname

 FROM gv$process p, gv$session s

 WHERE p.addr = s.paddr;

 

1.UNIX

kill -9 5745

ps -ef | grep pmon_$ORACLE_SID | awk '{print $2}' | xargs kill –9

#kill 一批会话

 

2. WINDOWS

orakill <instance_name> <spid>

如果会话已经在DB里killed，上面的SQL已经查不出spid,可以用下面的SQL查出SPID

 select addr, pid, spid

 FROM v$process p

 where addr in (select p.addr

 from v$process p

 where pid <> 1

 minus

 select s.paddr from v$session s)；

 

 

 

Killing Oracle Sessions

There are a number of ways to kill rogue sessions both within Oracle and externally.

• Identify the Session to be Killed
• ALTER SYSTEM KILL SESSION
• ALTER SYSTEM DISCONNECT SESSION
• The Windows Approach
• The UNIX Approach

Identify the Session to be Killed

Killing sessions can be very destructive if you kill the wrong session, so be very careful when identifying the session to be killed. If you kill a session belonging to a background process you will cause an instance crash.

Identify the offending session using the [G]V$SESSION and [G]V$PROCESS views as follows.

SET LINESIZE 100 COLUMN spid FORMAT A10COLUMN username FORMAT A10COLUMN program FORMAT A45SELECT s.inst_id,s.sid,s.serial#,p.spid,s.username,s.programFROM gv$session sJOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_idWHERE s.type != 'BACKGROUND';INST_ID SID SERIAL# SPID USERNAME PROGRAM---------- ---------- ---------- ---------- ---------- ---------------------------------------------1 30 15 3859 TEST [e-mail] (TNS V1-V3)1 23 287 3834 SYS [e-mail] (TNS V1-V3)1 40 387 4663 [e-mail] (J000)1 38 125 4665 [e-mail] (J001)SQL>

The SID and SERIAL# values of the relevant session can then be substituted into the commands in the following sections.

ALTER SYSTEM KILL SESSION

The basic syntax for killing a session is shown below.

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';

In a RAC environment, you optionally specify the INST_ID, shown when querying the GV$SESSION view. This allows you to kill a session on different RAC node.

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';

The KILL SESSION command doesn't actually kill the session. It merely asks the session to kill itself. In some situations, like waiting for a reply from a remote database or rolling back transactions, the session will not kill itself immediately and will wait for the current operation to complete. In these cases the session will have a status of "marked for kill". It will then be killed as soon as possible.

In addition to the syntax described above, you can add the IMMEDIATE clause.

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

This does not affect the work performed by the command, but it returns control back to the current session immediately, rather than waiting for confirmation of the kill.

If the marked session persists for some time you may consider killing the process at the operating system level. Before doing this it's worth checking to see if it is performing a rollback. You can do this by running this script (session_undo.sql). If the USED_UREC value is decreasing for the session in question you should leave it to complete the rollback rather than killing the session at the operating system level.

ALTER SYSTEM DISCONNECT SESSION

The ALTER SYSTEM DISCONNECT SESSION syntax is an alternative method for killing Oracle sessions. Unlike the KILL SESSION command which asks the session to kill itself, the DISCONNECT SESSIONcommand kills the dedicated server process (or virtual circuit when using Shared Sever), which is equivalent to killing the server process from the operating system. The basic syntax is similar to the KILL SESSION command with the addition of the POST_TRANSACTION clause. The SID and SERIAL#values of the relevant session can be substituted into one of the following statements.

SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

The POST_TRANSACTION clause waits for ongoing transactions to complete before disconnecting the session, while the IMMEDIATE clause disconnects the session and ongoing transactions are recovered immediately.

The POST_TRANSACTION and IMMEDIATE clauses can be used together, but the documentation states that in this case the IMMEDIATE clause is ignored. In addition, the syntax diagram suggests both clauses are optional, but in reality, one or both must be specified or you receive an error.

SQL> alter system disconnect session '30,7'; alter system disconnect session '30,7' * ERROR at line 1: ORA-02000: missing POST_TRANSACTION or IMMEDIATE keyword SQL>

This command means you should never need to switch to the operating system to kill sessions, which reduces the chances of killing the wrong process.

The Windows Approach

To kill the session on the Windows operating system, first identify the session, then substitute the relevant SID and SPID values into the following command issued from the command line.

C:> orakill ORACLE_SID spid

The session thread should be killed immediately and all resources released.

The UNIX Approach

To kill the session on UNIX or Linux operating systems, first identify the session, then substitute the relevant SPID into the following command.

% kill spid

If after a few minutes the process hasn't stopped, terminate the session using the following.

% kill -9 spid

If in doubt check that the SPID matches the UNIX PROCESSID shown using.

% ps -ef | grep ora

The session thread should be killed immediately and all resources released.

For more information see:

• End Session Clauses (Syntax Diagram)
• End Session Clauses (Description)

Hope this helps. Regards Tim...