Monday 2 January 2012

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#'>

2 comments: