Saturday, November 30, 2013

Trace & Kill User session in R12

How to trace user sessions from User Monitor Screen in Oracle Application (R12)and kill the session

Navigate to System Administrator-->  Security --> User --> Monitor
Note the User Name and Oracle Process ID

Now run this sql as 
&PID=Oracle Process ID
&USER_NAME=User Name


SELECT SUBSTR(d.user_name,1,30) "User Name" 
             ,      a.pid 
             ,      b.sid 
             ,      b.serial# 
             FROM   v$process a, v$session b, fnd_logins c, fnd_user d 
             WHERE  a.pid = c.pid 
             AND    c.pid = &PID 
             AND    d.user_name = UPPER('&USER_NAME') 
             AND    d.user_id = c.user_id 
             AND    a.addr = b.paddr 
             AND    c.end_time IS NULL

  Note the SID and SERIAL#     and pass the SID and SERIAL# in next sql within single quote.    
             
ALTER SYSTEM KILL SESSION '123, 1234';       --   <'SID, SERIAL#'>

No comments: