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