oracle inactive死连接
oracle 连接通常分为三种Active 表明当前连接的session正在进行处理, inactive 表明当前连接正在等待进行处理(不是通常理解上的执行完毕) 如何处理inactive 连接,防止过多占用资源,因为inactive 通常是由应用程序在关连接之间导致的问题。特别是有些连接未在finally 中进行关闭。kill 表示当前sql处理已取消,等特rollback. 这里可进行如下处理:
1. 命令行处理
windows 系统下oracle 提供orakill 进行杀,注意不能通过plsql 进行kill那种kill
select A.SID,B.SPID,A.SERIAL#,a.lockwait,A.USERNAME,A.OSUSER,a.logon_time,a.last_call_et/3600 LAST_HOUR,A.STATUS, 'orakill '||sid||' '||spid HOST_COMMAND,'alter system kill session '''||A.sid||','||A.SERIAL#||'''' SQL_COMMANDfrom v$session A,V$PROCESS B where A.PADDR=B.ADDR AND a.type!='BACKGROUND' and a.status='INACTIVE' and a.last_call_et>7200 and a.username='NETFORCE' and a.machine='imep1'
通过这样查询出来的语句进行手工kill
另一种是通过存储过程定时
CREATE OR REPLACE PROCEDURE "PROC_KILL_SESSION" AS v_sid number; v_serial number; killer varchar2(1000); CURSOR cursor_session_info is select sid,serial# from v$session where type!='BACKGROUND' and status='INACTIVE' and last_call_et>7200 and username='NETFORCE' and machine='imep1'; BEGIN open cursor_session_info; loop fetch cursor_session_info into v_sid,v_serial; exit when cursor_session_info%notfound;
killer:='alter system disconnect session '''||v_sid||','||v_serial||''' post_transaction immediate'; -- killer:='alter system kill session '''||v_sid||','||v_serial||''' immediate'; execute immediate killer; end loop; dbms_output.PUT_LINE(cursor_session_info%rowcount||' users with idle_time>7200s have been killed!'); close cursor_session_info; END; /
再通过编写一个job进行
BEGIN DBMS_JOB.SUBMIT( job => job, what => 'kill_session;', next_date => SYSDATE, interval => 'sysdate + 120/1440'); --120minutes
commit; END; /- |