加载中…
个人资料
小鹏ORACLE
小鹏ORACLE
  • 博客等级:
  • 博客积分:0
  • 博客访问:236,930
  • 关注人气:46
  • 获赠金笔:0支
  • 赠出金笔:0支
  • 荣誉徽章:
相关博文
推荐博文
谁看过这篇博文
加载中…
正文 字体大小:

oracle killed会话不释放的问题

(2012-05-03 09:10:01)
标签:

oracle

kill

session

immediate

杂谈

分类: oracle资料转载
一般情况下,在杀一个会话的时候,直接执行alter system kill session ‘sid,serial#’;
Administrator's Guide说,当session是active的时候,alter system kill session 只是将session标识为killed或者pseudo状态,并不会释放session持有的资源,所以我们在执行完alter system kill session 后,看会话还是一直存在。
oracle <wbr>killed会话不释放的问题
这种情况下可以使用 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 <wbr>killed会话不释放的问题
 
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

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 A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45

SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';

INST_ID SID SERIAL# SPID USERNAME PROGRAM
---------- ---------- ---------- ---------- ---------- ---------------------------------------------
1 30 15 3859 TEST sqlplus@oel5-11gr2.localdomain (TNS V1-V3)
1 23 287 3834 SYS sqlplus@oel5-11gr2.localdomain (TNS V1-V3)
1 40 387 4663 oracle@oel5-11gr2.localdomain (J000)
1 38 125 4665 oracle@oel5-11gr2.localdomain (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 SESSION command 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:

Hope this helps. Regards Tim...

0

阅读 评论 收藏 禁止转载 喜欢 打印举报/Report
  • 评论加载中,请稍候...
发评论

    发评论

    以上网友发言只代表其个人观点,不代表新浪网的观点或立场。

      

    新浪BLOG意见反馈留言板 电话:4000520066 提示音后按1键(按当地市话标准计费) 欢迎批评指正

    新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 会员注册 | 产品答疑

    新浪公司 版权所有