How To Find Process Informations of a Session(Kill Oracle Session)

dbtut

5 years ago

You can access the process information of a session in the Oracle database with the following query. Sometimes it is not possible to terminate a session with ‘kill session’. In this case, it is necessary to kill the process of the session via the operating system. With the following query, you can find out instance (inst_id) and process id (spid) information of the process.

SELECT s.inst_id,
       s.sid,
       s.serial#,
       s.sql_id,
       p.spid,
       s.username,
       s.program
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND'
AND s.SID=1111
AND s.serial#=1111

SELECT s.inst_id,

s.sid,

s.serial#,

s.sql_id,

p.spid,

s.username,

s.program

FROM gv$session s

JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id

WHERE s.type != 'BACKGROUND'

AND s.SID=1111

AND s.serial#=1111