-- -------------------------------------------------------
-- Roqet kill_session
-- kill_session sql script copyright 2005 roqet - http://www.roqet.org.
-- kill_session can be distributed and modified under the terms of the
-- GNU General Public License: http://www.gnu.org/copyleft/gpl.html
--   ~~~
--   'l'
--    -
-- -------------------------------------------------------

-- first find the session you want to kill:

PROMPT
PROMPT First; find the SID + SESSION you want to Kill..
PROMPT

COLUMN budget FORMAT A32
COLUMN USER_PROGRAM_OSUSER HEADING 'USER|PROGRAM|OSUSER' FORMAT A30 WORD_WRAPPED
COLUMN LOGON_TIME HEADING 'LOGON|TIME'

SELECT SID, 
SERIAL#, 
SUBSTR(USERNAME || '(' || PROGRAM || ')', 1,20) || OSUSER AS USER_PROGRAM_OSUSER, 
TO_CHAR(LOGON_TIME, 'MM/DD HH:MI') AS LOGON_TIME
FROM V$SESSION
WHERE USERNAME = 'PUB';

--eg:
--
--                      USER
--                      PROGRAM              LOGON
--       SID    SERIAL# OSUSER               TIME
------------ ---------- -------------------- -----------
--         8       2927 PUB(TOAD.exe)MYDOMN\ 03/15 10:08
--                      user.one
--
--        15      50770 PUB(plsqldev.exe)MYD 03/15 02:23
--                      OMN\use.two
--
--
-- 2 rows selected.

PROMPT
PROMPT Now, enter the SID and SERIAL#...
PROMPT

ACCEPT sid CHAR PROMPT 'Enter SID: '
ACCEPT ser CHAR PROMPT 'Enter SERIAL#: '
PROMPT

-- then kill the session:
ALTER SYSTEM KILL SESSION '&sid,&ser';