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;