CREATE OR REPLACE PROCEDURE P_KILL_ALL_INACTIVE_SESSIONS
IS
str_exec VARCHAR2 (10000);
BEGIN
FOR cur_session
IN (SELECT DISTINCT
'ALTER SYSTEM KILL SESSION '''
|| b.sid
|| ','
|| b.serial#
|| ''''
AS KILL_THEM_ALL
FROM sys.dba_ddl_locks a, sys.v_$session B
WHERE B.SID = a.session_id
AND owner NOT IN ('SYS',
'WMSYS',
'CTXSYS',
'DBSNMP')
AND B.TYPE <> 'BACKGROUND'
AND B.status = 'INACTIVE')
LOOP
BEGIN
str_exec := cur_session.KILL_THEM_ALL;
EXECUTE IMMEDIATE str_exec;
END;
END LOOP;
END;
/
Usage:
BEGIN
P_KILL_ALL_INACTIVE_SESSIONS;
END;
/
Ref: https://ozsoyler.blogspot.com/2014/10/how-to-kill-session-with-custom.html
IS
str_exec VARCHAR2 (10000);
BEGIN
FOR cur_session
IN (SELECT DISTINCT
'ALTER SYSTEM KILL SESSION '''
|| b.sid
|| ','
|| b.serial#
|| ''''
AS KILL_THEM_ALL
FROM sys.dba_ddl_locks a, sys.v_$session B
WHERE B.SID = a.session_id
AND owner NOT IN ('SYS',
'WMSYS',
'CTXSYS',
'DBSNMP')
AND B.TYPE <> 'BACKGROUND'
AND B.status = 'INACTIVE')
LOOP
BEGIN
str_exec := cur_session.KILL_THEM_ALL;
EXECUTE IMMEDIATE str_exec;
END;
END LOOP;
END;
/
Usage:
BEGIN
P_KILL_ALL_INACTIVE_SESSIONS;
END;
/
Ref: https://ozsoyler.blogspot.com/2014/10/how-to-kill-session-with-custom.html