How to kill an active session in Oracle apps

Hi, This post shows the queries to find an active sessions for an object and kill the session.

Note: Use the query to kill a session only if you have authorized to kill the session.

Query to see if the object has an active session:

SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name, session_id

FROM V$Locked_Object A, All_Objects B

WHERE A.Object_ID = B.Object_ID

and b.object_id =:p_object_id;

Query to find the sis and serial# based on session id that is fetched from the above query:

select * from v$session where sid = '2777';

Command to kill the session using sis and serial#:

alter system kill session '2777,23685';

Happy learning. Cheers!!

Query to get users who have a specific responsibility is attached to them in Oracle apps

Hi! This post shows you the query to fetch the users who have a particular responsibility to them.

SELECT fu.user_name,

  frt.responsibility_name,

  TO_CHAR(furg.start_date,'DD-MON-YYYY') start_date,

  furg.end_date,

  fu.DESCRIPTION 

FROM fnd_user fu ,

  fnd_user_resp_groups_direct furg ,

  fnd_responsibility_vl frt

WHERE fu.user_id                 = furg.user_id

AND frt.responsibility_id        = furg.responsibility_id

AND frt.application_id           = furg.responsibility_application_id

AND NVL(furg.end_date,sysdate+1) > sysdate

AND NVL(frt.end_date,sysdate +1) > sysdate

AND NVL(fu.end_date,sysdate  +1) > sysdate

AND frt.responsibility_name      = :p_resp_name;

Happy learning. Cheers!!