session_management.sql
-- ------------------------------------------------------------------------------------- File Name : session_management.sql-- Author : Pierre Montbleau-- Description : Oracle database session monitoring and management utility-- Purpose : Monitor active sessions, diagnose blocking, and manage database sessions-- Call Syntax : @F:\DBA\Scripts\session_management.sql (action) (session_id) (serial_num)-- Parameters : action - Action to perform (LIST, KILL, BLOCKING, DETAILS, STATS)-- session_id - Specific session ID to target (required for KILL, DETAILS)-- serial_num - Serial number for session (required for KILL)-- Last Modified: 2024-01-15-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set environment and validate parameters-- -----------------------------------------------------------------------------------
-- Set substitution variablesset verify OFF
CLEAR SCREENCOLUMN "_USER" NEW_VALUE _USER NOPRINTCOLUMN "_CONNECT_IDENTIFIER" NEW_VALUE _CONNECT_IDENTIFIER NOPRINTCOLUMN DATE1 NEW_VALUE DATE1 NOPRINTCOLUMN TIME1 NEW_VALUE TIME1 NOPRINT
SELECT USER AS "_USER", SYS_CONTEXT('USERENV', 'DB_NAME') AS "_CONNECT_IDENTIFIER", TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS DATE1, TO_CHAR(SYSDATE, 'HH24:MI:SS') AS TIME1FROM DUAL;
DEFINE DATE1 = &DATE1DEFINE TIME1 = &TIME1
-- Generate unique filename for output redirectionCOLUMN output_filename NEW_VALUE output_filename NOPRINTSELECT 'F:\DBA\Scripts\AI\Reports\DB_session_management_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_' || '&1' || '_' || '&2' || '_' || '&3' || '.log' AS output_filenameFROM DUAL;
DEFINE output_filename = &output_filename
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 200SET PAGESIZE 1000SET SERVEROUTPUT ON
-- Redirect output to unique fileSPOOL &output_filename
PROMPT Starting Session Management Script...PROMPT ======================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Action: &1PROMPT Session ID: &2PROMPT Serial #: &3PROMPT Timestamp: &DATE1 &TIME1PROMPT
-- Validate parametersDECLARE v_action VARCHAR2(20) := UPPER('&1'); v_sid NUMBER; v_serial NUMBER; v_session_exists NUMBER;BEGIN IF v_action NOT IN ('LIST', 'KILL', 'BLOCKING', 'DETAILS', 'STATS') THEN RAISE_APPLICATION_ERROR(-20001, 'Action must be LIST, KILL, BLOCKING, DETAILS, or STATS'); END IF; IF v_action IN ('KILL', 'DETAILS') THEN IF '&2' IS NULL THEN RAISE_APPLICATION_ERROR(-20002, 'Session ID required for ' || v_action || ' action'); END IF; BEGIN v_sid := TO_NUMBER('&2'); EXCEPTION WHEN VALUE_ERROR THEN RAISE_APPLICATION_ERROR(-20003, 'Session ID must be numeric'); END; -- Verify session exists SELECT COUNT(*) INTO v_session_exists FROM v$session WHERE sid = v_sid; IF v_session_exists = 0 THEN RAISE_APPLICATION_ERROR(-20004, 'Session ID ' || v_sid || ' does not exist'); END IF; END IF; IF v_action = 'KILL' THEN IF '&3' IS NULL THEN RAISE_APPLICATION_ERROR(-20005, 'Serial number required for KILL action'); END IF; BEGIN v_serial := TO_NUMBER('&3'); EXCEPTION WHEN VALUE_ERROR THEN RAISE_APPLICATION_ERROR(-20006, 'Serial number must be numeric'); END; -- Verify session with SID and SERIAL# exists SELECT COUNT(*) INTO v_session_exists FROM v$session WHERE sid = v_sid AND serial# = v_serial; IF v_session_exists = 0 THEN RAISE_APPLICATION_ERROR(-20007, 'Session ' || v_sid || ',' || v_serial || ' does not exist'); END IF; END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful');EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- ACTIVE SESSIONS LIST: Display current active sessions-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. ACTIVE SESSIONS OVERVIEWPROMPT ============================
COLUMN sid FORMAT 99999COLUMN serial# FORMAT 99999COLUMN username FORMAT A15COLUMN program FORMAT A25COLUMN machine FORMAT A20COLUMN status FORMAT A10COLUMN last_call_et FORMAT 999,999,999COLUMN sql_id FORMAT A15
BEGIN IF UPPER('&1') = 'LIST' THEN DBMS_OUTPUT.PUT_LINE('Displaying active sessions...'); END IF;END;/
SELECT s.sid, s.serial#, s.username, SUBSTR(s.program, 1, 25) as program, SUBSTR(s.machine, 1, 20) as machine, s.status, s.last_call_et, s.sql_idFROM v$session sWHERE s.status = 'ACTIVE' AND s.type = 'USER' AND s.username IS NOT NULLORDER BY s.last_call_et DESC;
-- ------------------------------------------------------------------------------------- SESSION DETAILS: Detailed information for specific session-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. SESSION DETAILSPROMPT ===================
COLUMN session_info FORMAT A30COLUMN value FORMAT A50
BEGIN IF UPPER('&1') = 'DETAILS' THEN DBMS_OUTPUT.PUT_LINE('Displaying details for session ' || '&2' || '...'); END IF;END;/
SELECT 'Username' as session_info, username as valueFROM v$sessionWHERE sid = &2UNION ALLSELECT 'Program', programFROM v$sessionWHERE sid = &2UNION ALLSELECT 'Machine', machineFROM v$sessionWHERE sid = &2UNION ALLSELECT 'Status', statusFROM v$sessionWHERE sid = &2UNION ALLSELECT 'Last Call (seconds)', TO_CHAR(last_call_et)FROM v$sessionWHERE sid = &2UNION ALLSELECT 'SQL ID', sql_idFROM v$sessionWHERE sid = &2UNION ALLSELECT 'Current SQL Text', SUBSTR(sql_text, 1, 50)FROM v$sqlWHERE sql_id = (SELECT sql_id FROM v$session WHERE sid = &2) AND ROWNUM = 1;
-- ------------------------------------------------------------------------------------- BLOCKING SESSIONS: Identify blocking and waiting sessions-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. BLOCKING SESSIONS ANALYSISPROMPT ==============================
COLUMN blocking_sid FORMAT 99999COLUMN blocked_sid FORMAT 99999COLUMN blocker_username FORMAT A15COLUMN blocker_program FORMAT A25COLUMN wait_event FORMAT A30COLUMN seconds_waiting FORMAT 999999
BEGIN IF UPPER('&1') IN ('BLOCKING', 'LIST') THEN DBMS_OUTPUT.PUT_LINE('Analyzing blocking sessions...'); END IF;END;/
SELECT s1.sid as blocking_sid, s2.sid as blocked_sid, s1.username as blocker_username, SUBSTR(s1.program, 1, 25) as blocker_program, s2.event as wait_event, s2.seconds_in_wait as seconds_waitingFROM v$session s1, v$session s2WHERE s1.sid = s2.blocking_session AND s1.blocking_session IS NULLORDER BY s1.sid, s2.sid;
-- ------------------------------------------------------------------------------------- SESSION STATISTICS: Resource usage by session-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. SESSION RESOURCE STATISTICSPROMPT ===============================
SET LIN 1000COLUMN sid FORMAT 99999COLUMN username FORMAT A15COLUMN cpu_seconds FORMAT 999,999COLUMN physical_reads FORMAT 999,999,999COLUMN logical_reads FORMAT 999,999,999COLUMN executions FORMAT 999,999,999COLUMN machine FORMAT A75COLUMN program FORMAT A75COLUMN sql_text FORMAT A100
BEGIN IF UPPER('&1') = 'STATS' THEN DBMS_OUTPUT.PUT_LINE('Displaying session statistics...'); END IF;END;/
SELECT ss.sid, s.username, s.serial#, s.program as program, s.machine as machine, ROUND(ss.value/100, 2) as cpu_seconds, st.disk_reads as physical_reads, st.buffer_gets as logical_reads, st.executions, st.sql_text as sql_text, st.sql_idFROM v$sesstat ssJOIN v$statname sn ON ss.statistic# = sn.statistic#JOIN v$session s ON ss.sid = s.sidLEFT JOIN v$sqlarea st ON s.sql_address = st.address AND s.sql_hash_value = st.hash_valueWHERE sn.name = 'CPU used by this session' AND ss.value > 0ORDER BY ss.value DESCFETCH FIRST 20 ROWS ONLY;
-- ------------------------------------------------------------------------------------- LONG RUNNING OPERATIONS: Monitor long-running session operations-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. LONG RUNNING OPERATIONSPROMPT ===========================
COLUMN sid FORMAT 99999COLUMN serial# FORMAT 99999COLUMN opname FORMAT A30COLUMN target FORMAT A30COLUMN elapsed_seconds FORMAT 999,999COLUMN time_remaining FORMAT 999,999COLUMN pct_complete FORMAT 999.99
BEGIN IF UPPER('&1') IN ('LIST', 'STATS') THEN DBMS_OUTPUT.PUT_LINE('Monitoring long-running operations...'); END IF;END;/
SELECT sid, serial#, opname, target, ROUND(elapsed_seconds) as elapsed_seconds, ROUND(time_remaining) as time_remaining, ROUND(sofar / NULLIF(totalwork, 0) * 100, 2) as pct_completeFROM v$session_longopsWHERE time_remaining > 0ORDER BY time_remaining DESC;
-- ------------------------------------------------------------------------------------- SESSION KILL OPERATION: Terminate specific session-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. SESSION MANAGEMENT OPERATIONSPROMPT =================================
BEGIN IF UPPER('&1') = 'KILL' THEN DECLARE v_sid NUMBER := &2; v_serial NUMBER := &3; v_username VARCHAR2(30); v_program VARCHAR2(48); BEGIN -- Get session details before killing SELECT username, program INTO v_username, v_program FROM v$session WHERE sid = v_sid AND serial# = v_serial; DBMS_OUTPUT.PUT_LINE('Attempting to kill session: ' || v_sid || ',' || v_serial); DBMS_OUTPUT.PUT_LINE('Username: ' || v_username); DBMS_OUTPUT.PUT_LINE('Program: ' || v_program); -- Kill the session EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || v_sid || ',' || v_serial || ''' IMMEDIATE'; DBMS_OUTPUT.PUT_LINE('Session kill command executed successfully'); DBMS_OUTPUT.PUT_LINE('Note: Session may take a few moments to fully terminate'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error killing session: ' || SQLERRM); DBMS_OUTPUT.PUT_LINE('You may need additional privileges to kill sessions'); END; ELSE DBMS_OUTPUT.PUT_LINE('No session management operations performed (action: ' || UPPER('&1') || ')'); END IF;END;/
-- ------------------------------------------------------------------------------------- SESSION WAIT EVENTS: Current wait events for sessions-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. CURRENT SESSION WAIT EVENTSPROMPT ===============================
COLUMN sid FORMAT 99999COLUMN event FORMAT A65COLUMN wait_time FORMAT 999999COLUMN state FORMAT A20COLUMN seconds_in_wait FORMAT 999,999,999
BEGIN IF UPPER('&1') IN ('LIST', 'DETAILS', 'BLOCKING') THEN DBMS_OUTPUT.PUT_LINE('Displaying current wait events...'); END IF;END;/
SELECT sid, event, wait_time, state, seconds_in_waitFROM v$session_waitWHERE event NOT LIKE 'SQL%' AND event NOT IN ('rdbms ipc message', 'pmon timer', 'smon timer') AND wait_time = 0ORDER BY seconds_in_wait DESCFETCH FIRST 15 ROWS ONLY;
-- ------------------------------------------------------------------------------------- SESSION SUMMARY: Overall session statistics-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. SESSION SUMMARY STATISTICSPROMPT ==============================
COLUMN session_type FORMAT A25COLUMN session_count FORMAT 999,999COLUMN description FORMAT A40
SELECT 'Active User Sessions' as session_type, COUNT(*) as session_count, 'User sessions with active SQL' as descriptionFROM v$sessionWHERE status = 'ACTIVE' AND type = 'USER'UNION ALLSELECT 'Inactive User Sessions', COUNT(*), 'User sessions not currently active'FROM v$sessionWHERE status = 'INACTIVE' AND type = 'USER'UNION ALLSELECT 'Background Sessions', COUNT(*), 'Oracle background processes'FROM v$sessionWHERE type = 'BACKGROUND'UNION ALLSELECT 'Blocking Sessions', COUNT(DISTINCT blocking_session), 'Sessions causing blocks'FROM v$sessionWHERE blocking_session IS NOT NULLORDER BY 2 DESC;
-- ------------------------------------------------------------------------------------- COMPLETION: Script completion summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT ======================================PROMPT SESSION MANAGEMENT COMPLETEDPROMPT ======================================PROMPT Action Performed: &1PROMPT Target Session: &2PROMPT Serial Number: &3PROMPT Completion Time: &DATE1 &TIME1PROMPT PROMPT Next Steps:PROMPT 1. Review active sessions and resource usagePROMPT 2. Monitor blocking sessions if identifiedPROMPT 3. Verify session operations completed successfullyPROMPT 4. Document any session management actions takenPROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment and stop spooling-- -----------------------------------------------------------------------------------
SPOOL OFF
CLEAR COLUMNSSET VERIFY ONSET TIMING OFF
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set environment and validate parameters-- -----------------------------------------------------------------------------------
-- Set substitution variablesset verify OFF
CLEAR SCREENCOLUMN "_USER" NEW_VALUE _USER NOPRINTCOLUMN "_CONNECT_IDENTIFIER" NEW_VALUE _CONNECT_IDENTIFIER NOPRINTCOLUMN DATE1 NEW_VALUE DATE1 NOPRINTCOLUMN TIME1 NEW_VALUE TIME1 NOPRINT
SELECT USER AS "_USER", SYS_CONTEXT('USERENV', 'DB_NAME') AS "_CONNECT_IDENTIFIER", TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS DATE1, TO_CHAR(SYSDATE, 'HH24:MI:SS') AS TIME1FROM DUAL;
DEFINE DATE1 = &DATE1DEFINE TIME1 = &TIME1
-- Generate unique filename for output redirectionCOLUMN output_filename NEW_VALUE output_filename NOPRINTSELECT 'F:\DBA\Scripts\AI\Reports\DB_session_management_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_' || '&1' || '_' || '&2' || '_' || '&3' || '.log' AS output_filenameFROM DUAL;
DEFINE output_filename = &output_filename
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 200SET PAGESIZE 1000SET SERVEROUTPUT ON
-- Redirect output to unique fileSPOOL &output_filename
PROMPT Starting Session Management Script...PROMPT ======================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Action: &1PROMPT Session ID: &2PROMPT Serial #: &3PROMPT Timestamp: &DATE1 &TIME1PROMPT
-- Validate parametersDECLARE v_action VARCHAR2(20) := UPPER('&1'); v_sid NUMBER; v_serial NUMBER; v_session_exists NUMBER;BEGIN IF v_action NOT IN ('LIST', 'KILL', 'BLOCKING', 'DETAILS', 'STATS') THEN RAISE_APPLICATION_ERROR(-20001, 'Action must be LIST, KILL, BLOCKING, DETAILS, or STATS'); END IF; IF v_action IN ('KILL', 'DETAILS') THEN IF '&2' IS NULL THEN RAISE_APPLICATION_ERROR(-20002, 'Session ID required for ' || v_action || ' action'); END IF; BEGIN v_sid := TO_NUMBER('&2'); EXCEPTION WHEN VALUE_ERROR THEN RAISE_APPLICATION_ERROR(-20003, 'Session ID must be numeric'); END; -- Verify session exists SELECT COUNT(*) INTO v_session_exists FROM v$session WHERE sid = v_sid; IF v_session_exists = 0 THEN RAISE_APPLICATION_ERROR(-20004, 'Session ID ' || v_sid || ' does not exist'); END IF; END IF; IF v_action = 'KILL' THEN IF '&3' IS NULL THEN RAISE_APPLICATION_ERROR(-20005, 'Serial number required for KILL action'); END IF; BEGIN v_serial := TO_NUMBER('&3'); EXCEPTION WHEN VALUE_ERROR THEN RAISE_APPLICATION_ERROR(-20006, 'Serial number must be numeric'); END; -- Verify session with SID and SERIAL# exists SELECT COUNT(*) INTO v_session_exists FROM v$session WHERE sid = v_sid AND serial# = v_serial; IF v_session_exists = 0 THEN RAISE_APPLICATION_ERROR(-20007, 'Session ' || v_sid || ',' || v_serial || ' does not exist'); END IF; END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful');EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- ACTIVE SESSIONS LIST: Display current active sessions-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. ACTIVE SESSIONS OVERVIEWPROMPT ============================
COLUMN sid FORMAT 99999COLUMN serial# FORMAT 99999COLUMN username FORMAT A15COLUMN program FORMAT A25COLUMN machine FORMAT A20COLUMN status FORMAT A10COLUMN last_call_et FORMAT 999,999,999COLUMN sql_id FORMAT A15
BEGIN IF UPPER('&1') = 'LIST' THEN DBMS_OUTPUT.PUT_LINE('Displaying active sessions...'); END IF;END;/
SELECT s.sid, s.serial#, s.username, SUBSTR(s.program, 1, 25) as program, SUBSTR(s.machine, 1, 20) as machine, s.status, s.last_call_et, s.sql_idFROM v$session sWHERE s.status = 'ACTIVE' AND s.type = 'USER' AND s.username IS NOT NULLORDER BY s.last_call_et DESC;
-- ------------------------------------------------------------------------------------- SESSION DETAILS: Detailed information for specific session-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. SESSION DETAILSPROMPT ===================
COLUMN session_info FORMAT A30COLUMN value FORMAT A50
BEGIN IF UPPER('&1') = 'DETAILS' THEN DBMS_OUTPUT.PUT_LINE('Displaying details for session ' || '&2' || '...'); END IF;END;/
SELECT 'Username' as session_info, username as valueFROM v$sessionWHERE sid = &2UNION ALLSELECT 'Program', programFROM v$sessionWHERE sid = &2UNION ALLSELECT 'Machine', machineFROM v$sessionWHERE sid = &2UNION ALLSELECT 'Status', statusFROM v$sessionWHERE sid = &2UNION ALLSELECT 'Last Call (seconds)', TO_CHAR(last_call_et)FROM v$sessionWHERE sid = &2UNION ALLSELECT 'SQL ID', sql_idFROM v$sessionWHERE sid = &2UNION ALLSELECT 'Current SQL Text', SUBSTR(sql_text, 1, 50)FROM v$sqlWHERE sql_id = (SELECT sql_id FROM v$session WHERE sid = &2) AND ROWNUM = 1;
-- ------------------------------------------------------------------------------------- BLOCKING SESSIONS: Identify blocking and waiting sessions-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. BLOCKING SESSIONS ANALYSISPROMPT ==============================
COLUMN blocking_sid FORMAT 99999COLUMN blocked_sid FORMAT 99999COLUMN blocker_username FORMAT A15COLUMN blocker_program FORMAT A25COLUMN wait_event FORMAT A30COLUMN seconds_waiting FORMAT 999999
BEGIN IF UPPER('&1') IN ('BLOCKING', 'LIST') THEN DBMS_OUTPUT.PUT_LINE('Analyzing blocking sessions...'); END IF;END;/
SELECT s1.sid as blocking_sid, s2.sid as blocked_sid, s1.username as blocker_username, SUBSTR(s1.program, 1, 25) as blocker_program, s2.event as wait_event, s2.seconds_in_wait as seconds_waitingFROM v$session s1, v$session s2WHERE s1.sid = s2.blocking_session AND s1.blocking_session IS NULLORDER BY s1.sid, s2.sid;
-- ------------------------------------------------------------------------------------- SESSION STATISTICS: Resource usage by session-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. SESSION RESOURCE STATISTICSPROMPT ===============================
SET LIN 1000COLUMN sid FORMAT 99999COLUMN username FORMAT A15COLUMN cpu_seconds FORMAT 999,999COLUMN physical_reads FORMAT 999,999,999COLUMN logical_reads FORMAT 999,999,999COLUMN executions FORMAT 999,999,999COLUMN machine FORMAT A75COLUMN program FORMAT A75COLUMN sql_text FORMAT A100
BEGIN IF UPPER('&1') = 'STATS' THEN DBMS_OUTPUT.PUT_LINE('Displaying session statistics...'); END IF;END;/
SELECT ss.sid, s.username, s.serial#, s.program as program, s.machine as machine, ROUND(ss.value/100, 2) as cpu_seconds, st.disk_reads as physical_reads, st.buffer_gets as logical_reads, st.executions, st.sql_text as sql_text, st.sql_idFROM v$sesstat ssJOIN v$statname sn ON ss.statistic# = sn.statistic#JOIN v$session s ON ss.sid = s.sidLEFT JOIN v$sqlarea st ON s.sql_address = st.address AND s.sql_hash_value = st.hash_valueWHERE sn.name = 'CPU used by this session' AND ss.value > 0ORDER BY ss.value DESCFETCH FIRST 20 ROWS ONLY;
-- ------------------------------------------------------------------------------------- LONG RUNNING OPERATIONS: Monitor long-running session operations-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. LONG RUNNING OPERATIONSPROMPT ===========================
COLUMN sid FORMAT 99999COLUMN serial# FORMAT 99999COLUMN opname FORMAT A30COLUMN target FORMAT A30COLUMN elapsed_seconds FORMAT 999,999COLUMN time_remaining FORMAT 999,999COLUMN pct_complete FORMAT 999.99
BEGIN IF UPPER('&1') IN ('LIST', 'STATS') THEN DBMS_OUTPUT.PUT_LINE('Monitoring long-running operations...'); END IF;END;/
SELECT sid, serial#, opname, target, ROUND(elapsed_seconds) as elapsed_seconds, ROUND(time_remaining) as time_remaining, ROUND(sofar / NULLIF(totalwork, 0) * 100, 2) as pct_completeFROM v$session_longopsWHERE time_remaining > 0ORDER BY time_remaining DESC;
-- ------------------------------------------------------------------------------------- SESSION KILL OPERATION: Terminate specific session-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. SESSION MANAGEMENT OPERATIONSPROMPT =================================
BEGIN IF UPPER('&1') = 'KILL' THEN DECLARE v_sid NUMBER := &2; v_serial NUMBER := &3; v_username VARCHAR2(30); v_program VARCHAR2(48); BEGIN -- Get session details before killing SELECT username, program INTO v_username, v_program FROM v$session WHERE sid = v_sid AND serial# = v_serial; DBMS_OUTPUT.PUT_LINE('Attempting to kill session: ' || v_sid || ',' || v_serial); DBMS_OUTPUT.PUT_LINE('Username: ' || v_username); DBMS_OUTPUT.PUT_LINE('Program: ' || v_program); -- Kill the session EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || v_sid || ',' || v_serial || ''' IMMEDIATE'; DBMS_OUTPUT.PUT_LINE('Session kill command executed successfully'); DBMS_OUTPUT.PUT_LINE('Note: Session may take a few moments to fully terminate'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error killing session: ' || SQLERRM); DBMS_OUTPUT.PUT_LINE('You may need additional privileges to kill sessions'); END; ELSE DBMS_OUTPUT.PUT_LINE('No session management operations performed (action: ' || UPPER('&1') || ')'); END IF;END;/
-- ------------------------------------------------------------------------------------- SESSION WAIT EVENTS: Current wait events for sessions-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. CURRENT SESSION WAIT EVENTSPROMPT ===============================
COLUMN sid FORMAT 99999COLUMN event FORMAT A65COLUMN wait_time FORMAT 999999COLUMN state FORMAT A20COLUMN seconds_in_wait FORMAT 999,999,999
BEGIN IF UPPER('&1') IN ('LIST', 'DETAILS', 'BLOCKING') THEN DBMS_OUTPUT.PUT_LINE('Displaying current wait events...'); END IF;END;/
SELECT sid, event, wait_time, state, seconds_in_waitFROM v$session_waitWHERE event NOT LIKE 'SQL%' AND event NOT IN ('rdbms ipc message', 'pmon timer', 'smon timer') AND wait_time = 0ORDER BY seconds_in_wait DESCFETCH FIRST 15 ROWS ONLY;
-- ------------------------------------------------------------------------------------- SESSION SUMMARY: Overall session statistics-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. SESSION SUMMARY STATISTICSPROMPT ==============================
COLUMN session_type FORMAT A25COLUMN session_count FORMAT 999,999COLUMN description FORMAT A40
SELECT 'Active User Sessions' as session_type, COUNT(*) as session_count, 'User sessions with active SQL' as descriptionFROM v$sessionWHERE status = 'ACTIVE' AND type = 'USER'UNION ALLSELECT 'Inactive User Sessions', COUNT(*), 'User sessions not currently active'FROM v$sessionWHERE status = 'INACTIVE' AND type = 'USER'UNION ALLSELECT 'Background Sessions', COUNT(*), 'Oracle background processes'FROM v$sessionWHERE type = 'BACKGROUND'UNION ALLSELECT 'Blocking Sessions', COUNT(DISTINCT blocking_session), 'Sessions causing blocks'FROM v$sessionWHERE blocking_session IS NOT NULLORDER BY 2 DESC;
-- ------------------------------------------------------------------------------------- COMPLETION: Script completion summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT ======================================PROMPT SESSION MANAGEMENT COMPLETEDPROMPT ======================================PROMPT Action Performed: &1PROMPT Target Session: &2PROMPT Serial Number: &3PROMPT Completion Time: &DATE1 &TIME1PROMPT PROMPT Next Steps:PROMPT 1. Review active sessions and resource usagePROMPT 2. Monitor blocking sessions if identifiedPROMPT 3. Verify session operations completed successfullyPROMPT 4. Document any session management actions takenPROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment and stop spooling-- -----------------------------------------------------------------------------------
SPOOL OFF
CLEAR COLUMNSSET VERIFY ONSET TIMING OFF
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------