-- get_sessions.sql
--
-- Lists all sessions for the database which one is connected to.
-- Then instructs how to kill an offending session.
--
set verify off
set newpage 0
set pagesize 55
set linesize 120
set recsep off
--
set pagesize 0
set termout off
column server_source new_value server
select lower(name) server_source from v$database;
column host_source new_value host
select lower(replace(host_name,'.fnal.gov','')) host_source from v$instance;
set termout on
set feedback off
PROMPT
SELECT 'Sessions on Server ' || UPPER('&server') ||
       ' as of ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI')
FROM DUAL;
set feedback on
set pagesize 55
PROMPT ======================================================================================================
--
column osuser     heading 'OS USER'  format a12
column program    heading 'PROGRAM'  format a27
column machine    heading 'MACHINE'  format a17
column logon_time heading 'LOG TIME' format a9

SELECT TO_CHAR(s.sid,'999') "SID",
       TO_CHAR(s.serial#,'999999') "SERIAL#",
       p.spid,
       s.osuser,
       s.type,
       REPLACE(s.program,'.fnal.gov','') "program",
       s.machine,
       TO_CHAR(s.logon_time,'DD-MON-YY') "logon_time"
FROM   v$process p,
       v$session s
WHERE  p.addr = s.paddr
ORDER BY s.sid;
--
PROMPT
PROMPT To kill a session, use its SID and SERIAL#:
PROMPT
PROMPT SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';;
PROMPT 
PROMPT Often the session is not killed immediately, but marked as killed (STATUS=KILLED).
set pagesize 0
set feedback off
SELECT 'To get rid of it, login to the Oracle server machine &host as oracle user.'
FROM DUAL;
--PROMPT To get rid of it, login to the Oracle server machine as oracle user.
PROMPT Identify the session thread via its SPID, Server Process ID, given above:
PROMPT
PROMPT > ps -ef | grep 'spid'
PROMPT
PROMPT and kill the process by OS command:
PROMPT
PROMPT > kill -9 'spid'
--
-- Restore defaults
--
set verify on
set newpage 1
set pagesize 14
set linesize 80
set recsep wrap
--
