!/bin/bash
ORACLE_SID="testdb"
ORACLE_HOME="/u01/app/oracle/product/11gR2/db"
INACTIVE_TIMEOUT=60 # 非活动超时时间(秒)
设置Oracle环境变量
export ORACLE_SID
export ORACLE_HOME
连接到Oracle数据库
sqlplus -S /nolog >> /home/oracle/kill_session_output.log<<EOF
CONNECT / AS SYSDBA
-- 查询非活动会话并杀死超时的会话
SET SERVEROUTPUT ON
DECLARE
v_sid V$SESSION.SID%TYPE;
v_serial V$SESSION.SERIAL#%TYPE;
CURSOR c_sessions IS
SELECT SID, SERIAL#
FROM V\$SESSION
WHERE STATUS = 'INACTIVE';
BEGIN
FOR session IN c_sessions LOOP
v_sid := session.SID;
v_serial := session.SERIAL#;
EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || v_sid || ',' || v_serial || '''';
DBMS_OUTPUT.PUT_LINE('Killed session: SID=' || v_sid || ', SERIAL#=' || v_serial);
END LOOP;
END;
/
EXIT;
EOF