Tuesday, August 12, 2025

Session Kill

 
/********************************************************************
   DYNAMIC SAFE KILLER - SNIPER & PURGE MODES
   → Includes protections for SYS/RDSADMIN
   → Auto-detects Scheduler Jobs vs User Sessions
********************************************************************/
SET SERVEROUTPUT ON
DECLARE
    -- =================================================================
    -- CONFIGURATION: CHOOSE ONE METHOD
    -- =================================================================
    -- METHOD A: Specific Session (Sniper Mode)
    v_sid         NUMBER        := &sid;          -- e.g., 123 (Or NULL if using Method B)
    v_serial      NUMBER        := &serial;       -- e.g., 4567 (Or NULL if using Method B)

    -- METHOD B: All Sessions for User (Purge Mode)
    -- Enter username in UPPERCASE. Leave NULL if using Method A.
    v_target_user VARCHAR2(128) := '&username';   -- e.g., 'SCOTT' or NULL
    -- =================================================================

    -- Internal Variables
    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;

    -- Procedure to perform the actual 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);

        -- 1. SCHEDULER JOB 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;

        -- 2. ALREADY DEAD CHECK
        IF p_status = 'KILLED' THEN
            DBMS_OUTPUT.PUT_LINE('Already KILLED. Moving to NUCLEAR PROCESS kill.');
            rdsadmin.rdsadmin_util.kill(p_sid, p_serial, 'PROCESS');
            DBMS_OUTPUT.PUT_LINE('OS Process terminated.');
            RETURN;
        END IF;

        -- 3. STANDARD KILL
        BEGIN
            rdsadmin.rdsadmin_util.kill(p_sid, p_serial, 'IMMEDIATE');
            DBMS_OUTPUT.PUT_LINE('KILL IMMEDIATE sent.');
            
            -- Quick check if it worked
            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('Session stubborn -> OS Process terminated.');
            END IF;
        EXCEPTION WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
        END;
    END;

BEGIN
    -- [NEW] 1. VISIBILITY: Tell the user exactly what mode we are in
    DBMS_OUTPUT.PUT_LINE('=== OPERATION START ===');
    DBMS_OUTPUT.PUT_LINE('Mode: ' || CASE WHEN v_target_user IS NOT NULL 
                                     THEN 'PURGE USER [' || v_target_user || ']'
                                     ELSE 'SNIPER [SID=' || v_sid || ']' END);

    -- SAFETY CHECK 1: Ensure input is valid
    IF v_sid IS NULL AND v_target_user IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('ERROR: You must provide either a SID/SERIAL or a USERNAME.');
        RETURN;
    END IF;

    -- SAFETY CHECK 2: Prevent killing yourself
    IF v_target_user = USER THEN
        DBMS_OUTPUT.PUT_LINE('ERROR: You cannot purge your own username ('||USER||') while connected.');
        RETURN;
    END IF;

    -- [NEW] SAFETY CHECK 3: Block mass-killing of privileged accounts
    IF UPPER(v_target_user) IN ('SYS','SYSTEM','RDSADMIN','DBSNMP','XDB') THEN
        DBMS_OUTPUT.PUT_LINE('BLOCKED: Cannot mass-kill privileged/system accounts ('||v_target_user||').');
        DBMS_OUTPUT.PUT_LINE('   If you must kill a session here, use Sniper Mode (SID/Serial) individually.');
        RETURN;
    END IF;

    -- EXECUTION LOOP
    FOR r IN c_sessions LOOP
        -- SAFETY CHECK 4: Background Processes are untouchable
        IF r.type = 'BACKGROUND' THEN
            DBMS_OUTPUT.PUT_LINE('SKIPPING SID ' || r.sid || ' (Background Process).');
            CONTINUE;
        END IF;
        
        -- SAFETY CHECK 5: Don't kill the current session
        IF r.sid = SYS_CONTEXT('USERENV', 'SID') THEN
             CONTINUE;
        END IF;

        -- EXECUTE
        kill_session(r.sid, r.serial#, r.program, r.status);
        v_killed_cnt := v_killed_cnt + 1;
    END LOOP;

    IF v_killed_cnt = 0 THEN
        DBMS_OUTPUT.PUT_LINE('No matching sessions found to kill.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('=== COMPLETED. Killed ' || v_killed_cnt || ' session(s). ===');
    END IF;
END;
/

==================================

Step 1: Create the Package Specification

This defines the "Public Interface"—the two commands your team is allowed to use.

SQL
CREATE OR REPLACE PACKAGE admin_kill_tools AS
    /****************************************************************
    * ADMIN_KILL_TOOLS
    * ----------------
    * Centralized session management for Oracle RDS.
    * Includes safety rails for background processes and system users.
    ****************************************************************/

    -- Sniper Mode: Kill a specific SID/Serial
    PROCEDURE kill_session(
        p_sid    IN NUMBER,
        p_serial IN NUMBER
    );

    -- Purge Mode: Kill ALL sessions for a specific user
    PROCEDURE purge_user(
        p_username IN VARCHAR2
    );

END admin_kill_tools;
/

Step 2: Create the Package Body

This contains the "Brain." It hides the complexity of checking for scheduler jobs, verifying background processes, and performing the kill.

SQL
CREATE OR REPLACE PACKAGE BODY admin_kill_tools AS

    -- PRIVATE HELPER: The actual logic that performs the kill
    -- (Not accessible directly by users)
    PROCEDURE do_kill_logic(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);

        -- 1. SCHEDULER JOB 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 Scheduler 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;

        -- 2. ALREADY DEAD CHECK
        IF p_status = 'KILLED' THEN
            DBMS_OUTPUT.PUT_LINE('Already KILLED status. Escaling to NUCLEAR PROCESS kill.');
            rdsadmin.rdsadmin_util.kill(p_sid, p_serial, 'PROCESS');
            DBMS_OUTPUT.PUT_LINE('OS Process terminated.');
            RETURN;
        END IF;

        -- 3. STANDARD KILL
        BEGIN
            rdsadmin.rdsadmin_util.kill(p_sid, p_serial, 'IMMEDIATE');
            DBMS_OUTPUT.PUT_LINE('KILL IMMEDIATE sent.');
            
            -- Wait 2 seconds and check
            DBMS_LOCK.SLEEP(2);
            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('Session stubborn -> OS Process terminated.');
            END IF;
        EXCEPTION WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Error during kill: ' || SQLERRM);
        END;
    END do_kill_logic;

    -------------------------------------------------------------------------
    -- PUBLIC PROCEDURE 1: KILL_SESSION (Sniper)
    -------------------------------------------------------------------------
    PROCEDURE kill_session(p_sid NUMBER, p_serial NUMBER) IS
        CURSOR c_target IS
            SELECT sid, serial#, username, type, program, status
            FROM v$session WHERE sid = p_sid AND serial# = p_serial;
        r c_target%ROWTYPE;
    BEGIN
        OPEN c_target; FETCH c_target INTO r;
        
        IF c_target%NOTFOUND THEN
            DBMS_OUTPUT.PUT_LINE('Session ' || p_sid || ',' || p_serial || ' not found.');
            CLOSE c_target; RETURN;
        END IF;
        CLOSE c_target;

        -- Safety: Background Process
        IF r.type = 'BACKGROUND' THEN
            DBMS_OUTPUT.PUT_LINE('BLOCKED: Cannot kill background process.');
            RETURN;
        END IF;

        -- Safety: Self-Kill
        IF r.sid = SYS_CONTEXT('USERENV', 'SID') THEN
            DBMS_OUTPUT.PUT_LINE('BLOCKED: You cannot kill your own current session.');
            RETURN;
        END IF;

        DBMS_OUTPUT.PUT_LINE('=== SNIPER KILL START ===');
        do_kill_logic(r.sid, r.serial#, r.program, r.status);
        DBMS_OUTPUT.PUT_LINE('=== FINISHED ===');
    END kill_session;

    -------------------------------------------------------------------------
    -- PUBLIC PROCEDURE 2: PURGE_USER (Shotgun)
    -------------------------------------------------------------------------
    PROCEDURE purge_user(p_username IN VARCHAR2) IS
        v_user_clean VARCHAR2(128) := UPPER(TRIM(p_username));
        v_count      NUMBER := 0;
    BEGIN
        DBMS_OUTPUT.PUT_LINE('=== PURGE USER START: ' || v_user_clean || ' ===');

        -- Safety: Privileged Accounts
        IF v_user_clean IN ('SYS','SYSTEM','RDSADMIN','DBSNMP','XDB','AUDSYS') THEN
            DBMS_OUTPUT.PUT_LINE('BLOCKED: Cannot mass-kill privileged account: ' || v_user_clean);
            RETURN;
        END IF;

        -- Safety: Self-Purge
        IF v_user_clean = USER THEN
            DBMS_OUTPUT.PUT_LINE('BLOCKED: You cannot purge yourself ('||USER||') while connected.');
            RETURN;
        END IF;

        -- Loop and Kill
        FOR r IN (
            SELECT sid, serial#, username, type, program, status
            FROM v$session 
            WHERE username = v_user_clean
            AND type != 'BACKGROUND'
            AND sid != SYS_CONTEXT('USERENV', 'SID')
        ) LOOP
            do_kill_logic(r.sid, r.serial#, r.program, r.status);
            v_count := v_count + 1;
        END LOOP;

        IF v_count = 0 THEN
            DBMS_OUTPUT.PUT_LINE('No active sessions found for user: ' || v_user_clean);
        ELSE
            DBMS_OUTPUT.PUT_LINE('=== PURGE COMPLETE. Killed ' || v_count || ' sessions. ===');
        END IF;
    END purge_user;

END admin_kill_tools;
/

Step 3: Deployment & Usage

Once you compile the package above, here is how you use it.

1. Enable Output (Important!) Since this runs inside a procedure, you must turn on output to see the logs.

SQL
SET SERVEROUTPUT ON;

2. Scenario A: A user "SCOTT" is blocking a deployment.

SQL
EXEC admin_kill_tools.purge_user('SCOTT');

3. Scenario B: A specific rogue query (SID 123, Serial 99).

SQL
EXEC admin_kill_tools.kill_session(123, 99);

Step 4: Permissions (Optional but Recommended)

If you want other team members to use this without giving them the powerful DBA role or ALTER SYSTEM privilege, grant them execute access on this package only.

SQL
GRANT EXECUTE ON admin_kill_tools TO junior_dba_user;

Would you like a companion "View" that shows exactly who is currently blocking others, so you know who to kill?



=================================
SET SERVEROUTPUT ON;
BEGIN
   FOR session_rec IN (
      SELECT sid, serial#
      FROM v$session
      WHERE username = UPPER('HR')
      AND sql_id = '7x9k2m3n4p5q'
      AND status = 'ACTIVE'
   )
   LOOP
      BEGIN
         rdsadmin.rdsadmin_util.kill(
            sid    => session_rec.sid,
            serial => session_rec.serial#
         );
         DBMS_OUTPUT.PUT_LINE('Session killed successfully: SID=' || session_rec.sid || ', SERIAL#=' || session_rec.serial#);
      EXCEPTION
         WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Error killing session SID=' || session_rec.sid || ', SERIAL#=' || session_rec.serial# || ': ' || SQLERRM);
            CONTINUE;
      END;
   END LOOP;

   IF SQL%ROWCOUNT = 0 THEN
      DBMS_OUTPUT.PUT_LINE('No active sessions found for username=HR and sql_id=7x9k2m3n4p5q');
   END IF;

EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
END;

/



SET SERVEROUTPUT ON;
DECLARE
   v_username VARCHAR2(128) := 'SCOTT'; -- Manually set username
   v_sql_id   VARCHAR2(13) := 'abc123xyz4567'; -- Manually set sql_id
BEGIN
   FOR session_rec IN (
      SELECT sid, serial#
      FROM v$session
      WHERE username = UPPER(v_username)
      AND sql_id = v_sql_id
      AND status = 'ACTIVE'
   )
   LOOP
      BEGIN
         rdsadmin.rdsadmin_util.kill(
            sid    => session_rec.sid,
            serial => session_rec.serial#
         );
         DBMS_OUTPUT.PUT_LINE('Session killed successfully: SID=' || session_rec.sid || ', SERIAL#=' || session_rec.serial#);
      EXCEPTION
         WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Error killing session SID=' || session_rec.sid || ', SERIAL#=' || session_rec.serial# || ': ' || SQLERRM);
            CONTINUE;
      END;
   END LOOP;
   IF SQL%ROWCOUNT = 0 THEN
      DBMS_OUTPUT.PUT_LINE('No active sessions found for username=' || UPPER(v_username) || ' and sql_id=' || v_sql_id);
   END IF;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
END;
/



SET SERVEROUTPUT ON SIZE UNLIMITED;
SET LONG 1000000;
SET LINESIZE 32767;
SET PAGESIZE 0;
SET TRIMSPOOL ON;
SET LONGCHUNKSIZE 1000000;

BEGIN
  FOR sql_rec IN (
    SELECT DISTINCT sql_id
    FROM v$sql
    WHERE sql_id IN ('your_sql_id1', 'your_sql_id2')  -- Replace with your SQL_IDs
    UNION
    SELECT sql_id
    FROM dba_hist_sqltext
    WHERE sql_id IN ('your_sql_id1', 'your_sql_id2')
  )
  LOOP
    DBMS_OUTPUT.PUT_LINE('SQL_ID: ' || sql_rec.sql_id);
    DBMS_OUTPUT.PUT_LINE('SQL Text:');
    FOR rec IN (
      SELECT sql_full_text
      FROM v$sql
      WHERE sql_id = sql_rec.sql_id
      AND rownum = 1
    )
    LOOP
      DBMS_OUTPUT.PUT_LINE(rec.sql_full_text);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Execution Plan:');
    FOR plan_rec IN (
      SELECT plan_table_output
      FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_rec.sql_id, NULL, 'ALL +NOTE +MEMSTATS'))
    )
    LOOP
      DBMS_OUTPUT.PUT_LINE(plan_rec.plan_table_output);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('----------------------------------------');
  END LOOP;
END;
/

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)