SET SERVEROUTPUT ON
DECLARE
-- =================================================================
-- CONFIGURATION (Tri-Mode)
-- =================================================================
-- 1. Sniper Mode (Specific Session)
v_sid_raw VARCHAR2(50) := TRIM('&sid');
v_serial_raw VARCHAR2(50) := TRIM('&serial');
-- 2. Purge Mode (All sessions for User)
v_target_user VARCHAR2(128) := TRIM('&username');
-- 3. SQL Killer Mode (All sessions running specific SQL)
v_target_sql VARCHAR2(13) := TRIM('&sql_id');
-- =================================================================
v_sid NUMBER;
v_serial NUMBER;
-- Protected Users List
TYPE t_protected IS TABLE OF VARCHAR2(30);
v_protected t_protected := t_protected('SYS','SYSTEM','RDSADMIN','DBSNMP','XDB','AUDSYS');
-- The "Wide Net" Cursor
CURSOR c_sessions IS
SELECT sid, serial#, username, type, program, status, sql_id
FROM v$session
WHERE (v_target_user IS NOT NULL AND username = v_target_user)
OR (v_sid IS NOT NULL AND sid = v_sid AND serial# = v_serial)
OR (v_target_sql IS NOT NULL AND sql_id = v_target_sql);
v_killed_cnt NUMBER := 0;
v_is_safe BOOLEAN := TRUE;
v_mode_msg VARCHAR2(100);
v_inputs_set NUMBER := 0;
-- Kill Logic
PROCEDURE kill_session(p_sid NUMBER, p_serial NUMBER, p_program VARCHAR2, p_status VARCHAR2) IS
l_job_name VARCHAR2(128);
l_check NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE(' Targeting SID: ' || p_sid || ' | Serial: ' || p_serial);
-- Scheduler Check
IF p_program LIKE '%(J%' THEN
BEGIN
SELECT job_name INTO l_job_name FROM dba_scheduler_running_jobs WHERE session_id = p_sid;
DBMS_OUTPUT.PUT_LINE(' Detected Job: ' || l_job_name);
DBMS_SCHEDULER.STOP_JOB(l_job_name, force => TRUE);
DBMS_OUTPUT.PUT_LINE('STOP_JOB executed.');
RETURN;
EXCEPTION WHEN OTHERS THEN NULL;
END;
END IF;
-- Already Dead Check
IF p_status = 'KILLED' THEN
DBMS_OUTPUT.PUT_LINE('Already KILLED. Moving to PROCESS kill.');
rdsadmin.rdsadmin_util.kill(p_sid, p_serial, 'PROCESS');
RETURN;
END IF;
-- Standard Kill
BEGIN
rdsadmin.rdsadmin_util.kill(p_sid, p_serial, 'IMMEDIATE');
DBMS_OUTPUT.PUT_LINE('KILL IMMEDIATE sent.');
SELECT COUNT(*) INTO l_check FROM v$session WHERE sid = p_sid AND serial# = p_serial;
IF l_check > 0 THEN
rdsadmin.rdsadmin_util.kill(p_sid, p_serial, 'PROCESS');
DBMS_OUTPUT.PUT_LINE(' OS Process terminated.');
END IF;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(' Error: ' || SQLERRM);
END;
END;
BEGIN
-- [STEP 1] Safe Number Conversion
BEGIN
v_sid := TO_NUMBER(v_sid_raw);
v_serial := TO_NUMBER(v_serial_raw);
EXCEPTION WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('ERROR: SID and Serial must be numbers.');
RETURN;
END;
-- Handle literal "NULL" strings
IF UPPER(v_target_user) = 'NULL' THEN v_target_user := NULL; END IF;
IF UPPER(v_target_sql) = 'NULL' THEN v_target_sql := NULL; END IF;
DBMS_OUTPUT.PUT_LINE('=== OPERATION START ===');
-- [SAFETY CHECK 0] Ambiguity Check (The "Pick One" Rule)
IF v_sid IS NOT NULL THEN v_inputs_set := v_inputs_set + 1; END IF;
IF v_target_user IS NOT NULL THEN v_inputs_set := v_inputs_set + 1; END IF;
IF v_target_sql IS NOT NULL THEN v_inputs_set := v_inputs_set + 1; END IF;
IF v_inputs_set > 1 THEN
DBMS_OUTPUT.PUT_LINE('ERROR: Ambiguous Input detected.');
DBMS_OUTPUT.PUT_LINE(' You provided multiple inputs. Please choose EXACTLY ONE mode:');
DBMS_OUTPUT.PUT_LINE(' 1. SID/Serial only (Sniper Mode)');
DBMS_OUTPUT.PUT_LINE(' 2. Username only (Purge Mode)');
DBMS_OUTPUT.PUT_LINE(' 3. SQL_ID only (SQL Killer Mode)');
RETURN;
END IF;
IF v_inputs_set = 0 THEN
DBMS_OUTPUT.PUT_LINE('ERROR: No input detected. Provide SID, Username, OR SQL_ID.');
RETURN;
END IF;
-- Determine Mode Message
IF v_target_user IS NOT NULL THEN
v_mode_msg := 'PURGE USER [' || v_target_user || ']';
ELSIF v_target_sql IS NOT NULL THEN
v_mode_msg := 'SQL KILLER [SQL_ID=' || v_target_sql || ']';
ELSE
v_mode_msg := 'SNIPER [SID=' || v_sid || ']';
END IF;
DBMS_OUTPUT.PUT_LINE('Mode: ' || v_mode_msg);
-- [SAFETY CHECK 2] Prevent Self-Kill
IF v_target_user = USER THEN
DBMS_OUTPUT.PUT_LINE('BLOCKED: You cannot purge yourself.');
RETURN;
END IF;
-- EXECUTION LOOP
FOR r IN c_sessions LOOP
v_is_safe := TRUE;
-- [SAFETY CHECK 3] Background
IF r.type = 'BACKGROUND' THEN
DBMS_OUTPUT.PUT_LINE('SKIPPING SID ' || r.sid || ' (Background Process).');
CONTINUE;
END IF;
-- [SAFETY CHECK 4] Self
IF r.sid = SYS_CONTEXT('USERENV', 'SID') THEN
CONTINUE;
END IF;
-- [SAFETY CHECK 5] Protected Users (Crucial for SQL_ID mode!)
-- Even if we kill by SQL_ID, we check WHO is running it.
-- If RDSADMIN is running the bad SQL, we MUST NOT kill it.
IF r.username IS NOT NULL THEN
FOR i IN 1..v_protected.COUNT LOOP
IF r.username = v_protected(i) THEN
DBMS_OUTPUT.PUT_LINE('BLOCKED SID ' || r.sid || ': User ' || r.username || ' is PROTECTED.');
v_is_safe := FALSE;
EXIT;
END IF;
END LOOP;
END IF;
IF v_is_safe THEN
kill_session(r.sid, r.serial#, r.program, r.status);
v_killed_cnt := v_killed_cnt + 1;
END IF;
END LOOP;
IF v_killed_cnt = 0 THEN
DBMS_OUTPUT.PUT_LINE('No actionable sessions found.');
ELSE
DBMS_OUTPUT.PUT_LINE('=== COMPLETED. Killed ' || v_killed_cnt || ' session(s). ===');
END IF;
END;
/
===============
SET SERVEROUTPUT ON
DECLARE
-- =================================================================
-- CONFIGURATION (Fixed for Empty Inputs)
-- =================================================================
-- We read these as STRINGS first to handle empty inputs safely.
v_sid_raw VARCHAR2(50) := TRIM('&sid');
v_serial_raw VARCHAR2(50) := TRIM('&serial');
v_target_user VARCHAR2(128) := TRIM('&username');
-- Now we convert them to numbers for logic
v_sid NUMBER;
v_serial NUMBER;
-- =================================================================
-- Protected Users List
TYPE t_protected IS TABLE OF VARCHAR2(30);
v_protected t_protected := t_protected('SYS','SYSTEM','RDSADMIN','DBSNMP','XDB','AUDSYS');
CURSOR c_sessions IS
SELECT sid, serial#, username, type, program, status
FROM v$session
WHERE (v_target_user IS NOT NULL AND username = v_target_user)
OR (v_target_user IS NULL AND sid = v_sid AND serial# = v_serial);
v_killed_cnt NUMBER := 0;
v_is_safe BOOLEAN := TRUE;
v_mode_msg VARCHAR2(100);
-- Internal Procedure
PROCEDURE kill_session(p_sid NUMBER, p_serial NUMBER, p_program VARCHAR2, p_status VARCHAR2) IS
l_job_name VARCHAR2(128);
l_check NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE(' Targeting SID: ' || p_sid || ' | Serial: ' || p_serial);
-- Scheduler Check
IF p_program LIKE '%(J%' THEN
BEGIN
SELECT job_name INTO l_job_name FROM dba_scheduler_running_jobs WHERE session_id = p_sid;
DBMS_OUTPUT.PUT_LINE(' Detected Job: ' || l_job_name);
DBMS_SCHEDULER.STOP_JOB(l_job_name, force => TRUE);
DBMS_OUTPUT.PUT_LINE(' STOP_JOB executed.');
RETURN;
EXCEPTION WHEN OTHERS THEN NULL;
END;
END IF;
-- Already Dead Check
IF p_status = 'KILLED' THEN
DBMS_OUTPUT.PUT_LINE(' Already KILLED. Moving to PROCESS kill.');
rdsadmin.rdsadmin_util.kill(p_sid, p_serial, 'PROCESS');
RETURN;
END IF;
-- Standard Kill
BEGIN
rdsadmin.rdsadmin_util.kill(p_sid, p_serial, 'IMMEDIATE');
DBMS_OUTPUT.PUT_LINE(' 🔪 KILL IMMEDIATE sent.');
SELECT COUNT(*) INTO l_check FROM v$session WHERE sid = p_sid AND serial# = p_serial;
IF l_check > 0 THEN
rdsadmin.rdsadmin_util.kill(p_sid, p_serial, 'PROCESS');
DBMS_OUTPUT.PUT_LINE(' OS Process terminated.');
END IF;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(' Error: ' || SQLERRM);
END;
END;
BEGIN
-- [STEP 1] Safe Conversion: Convert string inputs to Numbers
-- If empty, TO_NUMBER returns NULL (which is what we want)
BEGIN
v_sid := TO_NUMBER(v_sid_raw);
v_serial := TO_NUMBER(v_serial_raw);
EXCEPTION WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('ERROR: SID and Serial must be numbers.');
RETURN;
END;
-- Handle literal "NULL" string input
IF UPPER(v_target_user) = 'NULL' THEN v_target_user := NULL; END IF;
DBMS_OUTPUT.PUT_LINE('=== OPERATION START ===');
-- [SAFETY CHECK 0] Ambiguity Check
IF v_target_user IS NOT NULL AND (v_sid IS NOT NULL OR v_serial IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE('ERROR: Ambiguous Input detected.');
DBMS_OUTPUT.PUT_LINE(' Please choose ONE mode (Sniper OR Purge).');
RETURN;
END IF;
-- Display Mode
IF v_target_user IS NOT NULL THEN
v_mode_msg := 'PURGE USER [' || v_target_user || ']';
ELSE
v_mode_msg := 'SNIPER [SID=' || NVL(TO_CHAR(v_sid),'NULL') || ']';
END IF;
DBMS_OUTPUT.PUT_LINE('Mode: ' || v_mode_msg);
-- [SAFETY CHECK 1] No Input
IF v_target_user IS NULL AND v_sid IS NULL THEN
DBMS_OUTPUT.PUT_LINE('ERROR: No input detected.');
RETURN;
END IF;
-- [SAFETY CHECK 2] Self-Kill
IF v_target_user = USER THEN
DBMS_OUTPUT.PUT_LINE('BLOCKED: You cannot purge yourself.');
RETURN;
END IF;
-- EXECUTION LOOP
FOR r IN c_sessions LOOP
v_is_safe := TRUE;
-- [SAFETY CHECK 3] Background
IF r.type = 'BACKGROUND' THEN
DBMS_OUTPUT.PUT_LINE('SKIPPING SID ' || r.sid || ' (Background Process).');
CONTINUE;
END IF;
-- [SAFETY CHECK 4] Self (Current Session)
IF r.sid = SYS_CONTEXT('USERENV', 'SID') THEN
CONTINUE;
END IF;
-- [SAFETY CHECK 5] Protected Users
IF r.username IS NOT NULL THEN
FOR i IN 1..v_protected.COUNT LOOP
IF r.username = v_protected(i) THEN
DBMS_OUTPUT.PUT_LINE('BLOCKED SID ' || r.sid || ': User ' || r.username || ' is PROTECTED.');
v_is_safe := FALSE;
EXIT;
END IF;
END LOOP;
END IF;
IF v_is_safe THEN
kill_session(r.sid, r.serial#, r.program, r.status);
v_killed_cnt := v_killed_cnt + 1;
END IF;
END LOOP;
IF v_killed_cnt = 0 THEN
DBMS_OUTPUT.PUT_LINE('No actionable sessions found.');
ELSE
DBMS_OUTPUT.PUT_LINE('=== COMPLETED. Killed ' || v_killed_cnt || ' session(s). ===');
END IF;
END;
/
=============
SET SERVEROUTPUT ON SIZE UNLIMITED
SET LINESIZE 6000
SET PAGESIZE 0
SET VERIFY OFF
DECLARE
------------------------------------------------------------------
-- INPUT: CHANGE THESE ONLY
------------------------------------------------------------------
v_sid_serial_list VARCHAR2(4000) := '140,32001;255,1089';
v_username_list VARCHAR2(4000) := 'BATCH_JOB,REPORT_USER';
------------------------------------------------------------------
-- Internal
------------------------------------------------------------------
TYPE t_sid_rec IS RECORD (sid NUMBER, serial NUMBER, username VARCHAR2(30), status VARCHAR2(10), program VARCHAR2(100));
TYPE t_sid_tab IS TABLE OF t_sid_rec;
v_sessions t_sid_tab := t_sid_tab();
v_db_name VARCHAR2(30) := SYS_CONTEXT('USERENV','DB_NAME');
v_current_sid NUMBER := SYS_CONTEXT('USERENV','SID');
v_confirm VARCHAR2(10);
PROCEDURE log(p_msg VARCHAR2, p_emphasis BOOLEAN := FALSE) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(
TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS.FF3') ||
' | DB=' || v_db_name ||
CASE WHEN p_emphasis THEN ' | *** '||p_msg||' ***' ELSE ' | '||p_msg END
);
END;
PROCEDURE kill_one(p_sid NUMBER, p_serial NUMBER, p_method VARCHAR2) IS
v_action VARCHAR2(60) := CASE p_method
WHEN 'IMMEDIATE' THEN 'IMMEDIATE KILL'
WHEN 'PROCESS' THEN '*** AGGRESSIVE PROCESS KILL (kill -9) ***'
END;
BEGIN
log('KILL '||v_action||': SID='||p_sid||' SERIAL#='||p_serial, p_method = 'PROCESS');
BEGIN
rdsadmin.rdsadmin_util.kill(
sid => p_sid,
serial => p_serial,
method => p_method
);
log('SUCCESS: '||v_action||' issued.', p_method = 'PROCESS');
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -3135 THEN
log('INFO: Session already terminated (ORA-3135).');
ELSIF SQLCODE = -1013 THEN
log('WARN: Insufficient privileges.');
ELSE
log('ERROR: '||SQLERRM);
END IF;
END;
END;
BEGIN
log('=== SESSION KILL SAFETY CHECK ===', TRUE);
log('Current Session SID: '||v_current_sid);
------------------------------------------------------------------
-- STEP 1: COLLECT AND PREVIEW SESSIONS
------------------------------------------------------------------
IF v_sid_serial_list IS NOT NULL THEN
FOR rec IN (
WITH pairs AS (
SELECT TRIM(REGEXP_SUBSTR(v_sid_serial_list, '[^;]+', 1, LEVEL)) AS pair
FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT(v_sid_serial_list, ';') + 1
)
SELECT
TO_NUMBER(TRIM(REGEXP_SUBSTR(pair, '[^,]+', 1, 1))) AS sid,
TO_NUMBER(TRIM(REGEXP_SUBSTR(pair, '[^,]+', 1, 2))) AS serial
FROM pairs
WHERE REGEXP_LIKE(pair, '^[0-9]+,[0-9]+$')
) LOOP
IF rec.sid != v_current_sid THEN
FOR s IN (
SELECT sid, serial#, username, status, program
FROM v$session
WHERE sid = rec.sid AND serial# = rec.serial
) LOOP
v_sessions.EXTEND;
v_sessions(v_sessions.LAST) := s;
log('QUEUED (SID): SID='||s.sid||' SERIAL#='||s.serial#||' USER='||NVL(s.username,'<null>')||' STATUS='||s.status||' PROGRAM='||SUBSTR(s.program,1,50));
END LOOP;
END IF;
END LOOP;
END IF;
IF v_username_list IS NOT NULL THEN
FOR u IN (
SELECT TRIM(REGEXP_SUBSTR(v_username_list, '[^,]+', 1, LEVEL)) AS username
FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT(v_username_list, ',') + 1
) LOOP
FOR s IN (
SELECT sid, serial#, username, status, program
FROM v$session
WHERE UPPER(username) = UPPER(u.username)
AND type = 'USER'
AND status IN ('ACTIVE', 'INACTIVE', 'KILLED')
AND sid != v_current_sid
AND username NOT LIKE 'RDS%'
AND program NOT LIKE '%(P%)'
) LOOP
IF NOT EXISTS (
SELECT 1 FROM TABLE(v_sessions) v
WHERE v.sid = s.sid AND v.serial = s.serial#
) THEN
v_sessions.EXTEND;
v_sessions(v_sessions.LAST) := s;
log('QUEUED (user '||u.username||'): SID='||s.sid||' SERIAL#='||s.serial#||' STATUS='||s.status||' PROGRAM='||SUBSTR(s.program,1,50));
END IF;
END LOOP;
END LOOP;
END IF;
------------------------------------------------------------------
-- STEP 2: SHOW PREVIEW
------------------------------------------------------------------
IF v_sessions.COUNT = 0 THEN
log('No sessions found to kill. Exiting safely.');
RETURN;
END IF;
log('=== PREVIEW: '||v_sessions.COUNT||' SESSIONS TO BE KILLED ===', TRUE);
FOR i IN 1..v_sessions.COUNT LOOP
log(' ['||i||'] SID='||v_sessions(i).sid||
' SERIAL#='||v_sessions(i).serial||
' USER='||NVL(v_sessions(i).username,'<null>')||
' STATUS='||v_sessions(i).status||
' PROGRAM='||SUBSTR(v_sessions(i).program,1,60));
END LOOP;
------------------------------------------------------------------
-- STEP 3: CONFIRM BEFORE KILL
------------------------------------------------------------------
log('DO YOU WANT TO PROCEED WITH KILL? (Type YES to continue)', TRUE);
v_confirm := UPPER(TRIM('&CONFIRM_KILL'));
IF v_confirm != 'YES' THEN
log('KILL ABORTED BY USER. No action taken.');
RETURN;
END IF;
log('KILL CONFIRMED. Proceeding...', TRUE);
------------------------------------------------------------------
-- STEP 4: EXECUTE KILLS
------------------------------------------------------------------
FOR i IN 1..v_sessions.COUNT LOOP
kill_one(v_sessions(i).sid, v_sessions(i).serial, 'IMMEDIATE');
DBMS_LOCK.SLEEP(1);
kill_one(v_sessions(i).sid, v_sessions(i).serial, 'PROCESS');
END LOOP;
log('=== ALL '||v_sessions.COUNT||' SESSIONS KILLED SUCCESSFULLY ===', TRUE);
EXCEPTION
WHEN OTHERS THEN
log('FATAL ERROR: '||SQLERRM, TRUE);
RAISE;
END;
/
SET SERVEROUTPUT ON SIZE UNLIMITED
SET LINESIZE 6000
SET PAGESIZE 0
SET VERIFY OFF
DECLARE
------------------------------------------------------------------
-- INPUT: CHANGE THESE ONLY
------------------------------------------------------------------
v_sid_serial_list VARCHAR2(4000) := '140,32001;255,1089';
v_username_list VARCHAR2(4000) := 'BATCH_JOB,REPORT_USER';
------------------------------------------------------------------
-- Internal
------------------------------------------------------------------
TYPE t_sid_rec IS RECORD (sid NUMBER, serial NUMBER, username VARCHAR2(30), status VARCHAR2(10), program VARCHAR2(100));
TYPE t_sid_tab IS TABLE OF t_sid_rec;
v_sessions t_sid_tab := t_sid_tab();
v_db_name VARCHAR2(30) := SYS_CONTEXT('USERENV','DB_NAME');
v_current_sid NUMBER := SYS_CONTEXT('USERENV','SID');
v_confirm VARCHAR2(10);
PROCEDURE log(p_msg VARCHAR2, p_emphasis BOOLEAN := FALSE) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(
TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS.FF3') ||
' | DB=' || v_db_name ||
CASE WHEN p_emphasis THEN ' | *** '||p_msg||' ***' ELSE ' | '||p_msg END
);
END;
PROCEDURE kill_one(p_sid NUMBER, p_serial NUMBER, p_method VARCHAR2) IS
v_action VARCHAR2(60) := CASE p_method
WHEN 'IMMEDIATE' THEN 'IMMEDIATE KILL'
WHEN 'PROCESS' THEN '*** AGGRESSIVE PROCESS KILL (kill -9) ***'
END;
BEGIN
log('KILL '||v_action||': SID='||p_sid||' SERIAL#='||p_serial, p_method = 'PROCESS');
BEGIN
rdsadmin.rdsadmin_util.kill(
sid => p_sid,
serial => p_serial,
method => p_method
);
log('SUCCESS: '||v_action||' issued.', p_method = 'PROCESS');
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -3135 THEN
log('INFO: Session already terminated (ORA-3135).');
ELSIF SQLCODE = -1013 THEN
log('WARN: Insufficient privileges.');
ELSE
log('ERROR: '||SQLERRM);
END IF;
END;
END;
BEGIN
log('=== SESSION KILL SAFETY CHECK ===', TRUE);
log('Current Session SID: '||v_current_sid);
------------------------------------------------------------------
-- STEP 1: COLLECT AND PREVIEW SESSIONS
------------------------------------------------------------------
IF v_sid_serial_list IS NOT NULL THEN
FOR rec IN (
WITH pairs AS (
SELECT TRIM(REGEXP_SUBSTR(v_sid_serial_list, '[^;]+', 1, LEVEL)) AS pair
FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT(v_sid_serial_list, ';') + 1
)
SELECT
TO_NUMBER(TRIM(REGEXP_SUBSTR(pair, '[^,]+', 1, 1))) AS sid,
TO_NUMBER(TRIM(REGEXP_SUBSTR(pair, '[^,]+', 1, 2))) AS serial
FROM pairs
WHERE REGEXP_LIKE(pair, '^[0-9]+,[0-9]+$')
) LOOP
IF rec.sid != v_current_sid THEN
FOR s IN (
SELECT sid, serial#, username, status, program
FROM v$session
WHERE sid = rec.sid AND serial# = rec.serial
) LOOP
v_sessions.EXTEND;
v_sessions(v_sessions.LAST) := s;
log('QUEUED (SID): SID='||s.sid||' SERIAL#='||s.serial#||' USER='||NVL(s.username,'<null>')||' STATUS='||s.status||' PROGRAM='||SUBSTR(s.program,1,50));
END LOOP;
END IF;
END LOOP;
END IF;
IF v_username_list IS NOT NULL THEN
FOR u IN (
SELECT TRIM(REGEXP_SUBSTR(v_username_list, '[^,]+', 1, LEVEL)) AS username
FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT(v_username_list, ',') + 1
) LOOP
FOR s IN (
SELECT sid, serial#, username, status, program
FROM v$session
WHERE UPPER(username) = UPPER(u.username)
AND type = 'USER'
AND status IN ('ACTIVE', 'INACTIVE', 'KILLED')
AND sid != v_current_sid
AND username NOT LIKE 'RDS%'
AND program NOT LIKE '%(P%)'
) LOOP
IF NOT EXISTS (
SELECT 1 FROM TABLE(v_sessions) v
WHERE v.sid = s.sid AND v.serial = s.serial#
) THEN
v_sessions.EXTEND;
v_sessions(v_sessions.LAST) := s;
log('QUEUED (user '||u.username||'): SID='||s.sid||' SERIAL#='||s.serial#||' STATUS='||s.status||' PROGRAM='||SUBSTR(s.program,1,50));
END IF;
END LOOP;
END LOOP;
END IF;
------------------------------------------------------------------
-- STEP 2: SHOW PREVIEW
------------------------------------------------------------------
IF v_sessions.COUNT = 0 THEN
log('No sessions found to kill. Exiting safely.');
RETURN;
END IF;
log('=== PREVIEW: '||v_sessions.COUNT||' SESSIONS TO BE KILLED ===', TRUE);
FOR i IN 1..v_sessions.COUNT LOOP
log(' ['||i||'] SID='||v_sessions(i).sid||
' SERIAL#='||v_sessions(i).serial||
' USER='||NVL(v_sessions(i).username,'<null>')||
' STATUS='||v_sessions(i).status||
' PROGRAM='||SUBSTR(v_sessions(i).program,1,60));
END LOOP;
------------------------------------------------------------------
-- STEP 3: CONFIRM BEFORE KILL
------------------------------------------------------------------
log('DO YOU WANT TO PROCEED WITH KILL? (Type YES to continue)', TRUE);
v_confirm := UPPER(TRIM('&CONFIRM_KILL'));
IF v_confirm != 'YES' THEN
log('KILL ABORTED BY USER. No action taken.');
RETURN;
END IF;
log('KILL CONFIRMED. Proceeding...', TRUE);
------------------------------------------------------------------
-- STEP 4: EXECUTE KILLS
------------------------------------------------------------------
FOR i IN 1..v_sessions.COUNT LOOP
kill_one(v_sessions(i).sid, v_sessions(i).serial, 'IMMEDIATE');
DBMS_LOCK.SLEEP(1);
kill_one(v_sessions(i).sid, v_sessions(i).serial, 'PROCESS');
END LOOP;
log('=== ALL '||v_sessions.COUNT||' SESSIONS KILLED SUCCESSFULLY ===', TRUE);
EXCEPTION
WHEN OTHERS THEN
log('FATAL ERROR: '||SQLERRM, TRUE);
RAISE;
END;
/
No comments:
Post a Comment