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