session.getKillInfo.sql
Displays active sessions and cmds to kill them (RAC Version). Modify IGNORE_USERn as needed for more concise display.
-- session.getKillInfo.sql
-- Purpose: Get Session to Kill Info.
SET VERIFY off
SET LINESIZE 145
SET PAGESIZE 9999
COLUMN sid FORMAT 9999 HEADING 'SID'
COLUMN username FORMAT a12 HEADING 'O/S User' JUSTIFY right
COLUMN program FORMAT a26 HEADING 'Session Program' TRUNC
prompt
prompt +--------------+
prompt | Session Info |
prompt +--------------+
SELECT s.inst_id,s.sid,s.serial#,p.spid,s.username,s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND'
AND s.username NOT IN('DBSNMP','SYS','SYSTEM')
AND s.username NOT IN('IGNORE_USER1','IGNORE_USER2','IGNORE_USER3');
SELECT 'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ',@' || s.inst_id || ''' IMMEDIATE;' AS "CMD"
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND'
AND s.username NOT IN('DBSNMP','SYS','SYSTEM')
AND s.username NOT IN('ACTIVITYMANAGER','PORTAL','SAFUSER');