文本转载自《http://www.2cto.com/database/201204/127239.html》
-
一般情况下,在杀一个会话的时候,直接执行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 www.2cto.com (http://docs.oracle.com/cd/B28359_01/server.111/b28286/toc.htm#BEGIN)里对Immediate的解释是:IMMEDIATE Specify IMMEDIATE to instruct OracleDatabase to roll back ongoing transactions, release all session locks, recover the entire session state,and return control to you immediately.另外我们也可以使用alter system disconnect sessionThe 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, withoutwaiting 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, www.2cto.comthen 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杀进程终止会话1 SELECT spid, osuser, s.program, schemaname2 FROM gv$process p, gv$session s3 WHERE p.addr = s.paddr;1.UNIXkill -9 5745ps -ef | grep pmon_$ORACLE_SID | awk '{print $2}' | xargs kill –9 #kill 一批会话2. WINDOWSorakill <instance_name> <spid>如果会话已经在DB里killed,上面的SQL已经查不出spid,可以用下面的SQL查出SPID1 select addr, pid, spid2 FROM v$process p3 where addr in (select p.addr4 from v$process p5 where pid <> 16 minus www.2cto.com7 select s.paddr from v$session s)作者 DB&*NIX