Oracle存储过程_踢掉锁源头连接（已锁2分钟且该连接处于未活动状态）

CREATE OR REPLACE PROCEDURE SYS.DB_KILL_LOCK_CLIENTS AUTHID DEFINER AS
	JOB_NO       NUMBER;
	NUM_OF_KILLS NUMBER := 0;
BEGIN
	FOR REC IN (SELECT*FROM(SELECT r.root_sid,
         s.serial#,
         r.blocked_num,
         trunc( r.avg_wait_seconds) AS avg_wait_seconds ,
         s.username,
        s.status,
        s.event,
        s.MACHINE,
         s.PROGRAM,
        s.sql_id,
        s.prev_sql_id
FROM
    (SELECT root_sid,
         avg(seconds_in_wait) AS avg_wait_seconds,
        count(*) - 1 AS blocked_num
    FROM
        (SELECT CONNECT_BY_ROOT sid AS root_sid,
         seconds_in_wait
        FROM v$session start
        WITH blocking_session is NULL connect by prior sid =  blocking_session)
        GROUP BY  root_sid
        HAVING count(*) > 1) r, v$session s
    WHERE r.root_sid = s.sid) WHERE avg_wait_seconds > 120 and status='INACTIVE' ) LOOP
		EXECUTE IMMEDIATE 'alter system disconnect session ''' || REC.ROOT_SID || ', ' ||
											REC.SERIAL# || '''immediate';

		DBMS_OUTPUT.PUT_LINE('. killed locally ' || JOB_NO);
		NUM_OF_KILLS := NUM_OF_KILLS + 1;
	END LOOP;
END DB_KILL_LOCK_CLIENTS;
/