Friday, January 10, 2014

How to display session information, followed by a kill-statement

Often, my customers ask me to kill a runaway session for them, typically this will be in the form "Can you please kill session with session id 170 for us?. It's urgent!"

Before I do so, I'd like to be one houndred percent certain that I kill the right session.
To help me in such situations, I execute the script below. It will take one parameter, the sessions SID, and echo back some basic information about what the session is doing. Finally it prints a "kill session" command, which I can choose to execute if it was indeed the right session.

set verify           off
set linesize 200
col "os process"     format a30
col "os process id"  format a10
col "osuser"         format a15
col "schemaname"     format a20
col "client program" format a20
col "client name"    format a20
col "session type"   format a20
col status           format a10
col "session type"   format a15
cle scr
prompt ===================================================
prompt This script will print basic information about a
prompt session.
prompt
accept SID prompt 'Pls enter SID of session: '
prompt ===================================================
prompt  Information about database session &&SID
prompt ===================================================
column host_name new_value v_hostname;

set termout off
select host_name
from   v$instance;
set termout on

select
        p.program "os process",
        p.spid "os process id",
        p.username "osuser",
        s.sid,s.serial# "serial num",
        lower(s.schemaname) "schemaname",
        lower(s.osuser) "client name",
        s.program "client program",
        lower(s.status) "status",
        lower(s.type) "session type"
from v$process p, v$session s
where p.program not like 'oracle@v_hostname (%'
and p.addr = s.paddr
and p.addr = (select paddr from v$session where sid=&&SID)
order by s.username asc
/
set heading off
prompt ===================================================
prompt  Kill statement for session &&SID:
prompt ===================================================
select 'alter system kill session ''' || s.sid || ',' || s.serial# || ''';'
from   v$process p, v$session s
where  p.program not like 'oracle@v_hostname (%'
and    p.addr = s.paddr
and    p.addr = (select paddr from v$session where sid=&&SID)
order by s.username asc
/

exit

No comments:

Post a Comment