===========================================================
Disconnect expired shared sessions
===========================================================
作者: zhouwf0726(http://zhouwf0726.itpub.net)
发表于:2007.12.20 18:45
分类: oracle管理
出处：http://zhouwf0726.itpub.net/post/9689/444082
---------------------------------------------------------------
发表于:2007.12.20 18:45
分类: oracle管理
出处：http://zhouwf0726.itpub.net/post/9689/444082
---------------------------------------------------------------
Disconnect expired shared sessions:
CREATE OR REPLACE PROCEDURE p_kill_session_leo
(pi_days NUMBER)
AS
CURSOR c_kill
IS
SELECT s.sid, s.serial#
FROM v$circuit c, v$dispatcher d, v$session s
WHERE c.dispatcher = d.paddr
AND c.saddr = s.saddr
AND SYSDATE-s.logon_time > pi_days
AND s.status != 'ACTIVE';
BEGIN
FOR r_kill IN c_kill
LOOP
DBMS_OUTPUT.PUT_LINE ('alter system disconnect session '''||r_kill.sid||','||r_kill.serial#||''' immediate');
-- EXECUTE IMMEDIATE ''alter system disconnect session '''||r_kill.sid||','||r_kill.serial#||''' immediate'';
END LOOP;
EXCEPTION
WHEN OTHERS
THEN NULL;
END;
/