定时杀死长会话或锁

kill_long_session.sh

#!/bin/bash
su - oracle -c "sqlplus / as sysdba"<<EOF
@/home/oracle/kill_long_session.sql
EOF

　

kill_long_session.sql

beginfor cur in (select 'alter system kill session '''||sid||','||serial#||''' immediate' killfrom  v$session where status='ACTIVE' and username='NC65' and last_call_et>500) loopbeginexecute immediate cur.kill;end;end loop;
end;
/beginfor cur in (select 'alter system kill session '''||sid||','||serial#||''' immediate' killfrom  v$session where status='INACTIVE' and username is not null and logon_time<sysdate-3) loopbeginexecute immediate cur.kill;end;end loop;
end;
/

　　

crontab -l

*/5 * * * * sh /home/oracle/kill_long_session.sh

　　

#!/bin/bash
su - oracle -c "sqlplus / as sysdba<<eof
exec system.kill_session();
eof"

　　

create or replace procedure kill_session authid current_user is
var_sql_text varchar2(100);
beginselect 'alter system kill session ''' || sid || ',' || serial# ||''' immediate'into var_sql_textfrom v$sessionwhere sid = (select final_blocking_sessionfrom   v$sessionwhere status = 'ACTIVE'and final_blocking_session is not nullgroup by final_blocking_sessionhaving count(*) > 5) and rownum=1;EXECUTE IMMEDIATE var_sql_text;EXCEPTIONWHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE('No data found');end kill_session;