定时杀死长会话或锁

kill_long_session.sh

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

　

kill_long_session.sql

begin
        for cur in (select 'alter system kill session '''||sid||','||serial#||''' immediate' kill
                from  v$session where status='ACTIVE' and username='NC65' and last_call_et>500) loop
        begin
                execute immediate cur.kill;
        end;
        end loop;
end;
/

begin
        for cur in (select 'alter system kill session '''||sid||','||serial#||''' immediate' kill
                from  v$session where status='INACTIVE' and username is not null and logon_time<sysdate-3) loop
        begin
                execute 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);
begin
    select 'alter system kill session ''' || sid || ',' || serial# ||
           ''' immediate'
      into var_sql_text
      from v$session
     where sid = (select final_blocking_session
                    from   v$session
                   where status = 'ACTIVE'
                     and final_blocking_session is not null
                   group by final_blocking_session
                  having count(*) > 5) and rownum=1;
    EXECUTE IMMEDIATE var_sql_text;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('No data found');
  end kill_session;