How to identify blocked and blocking sessions in Oracle Financials

In order to find blocked sessions execute: 

select ses.MODULE, ses.ACTION, ses.BLOCKING_SESSION, ses.* from v$session seswhere ses.BLOCKING_SESSION is not null; 

In order to find blocking sessions execute: 

select s.sid as blocking_sid, s.serial#  , p.pid, p.spid, p.PGA_USED_MEM, s.LOGON_TIME , s.LOCKWAIT, s.MODULE, s.ACTION, s.*
from v$session s, v$process p
where p.ADDR = s.paddr  and s.sid in (select ses.BLOCKING_SESSION from v$session ses  where ses.BLOCKING_SESSION is not null); 

In order to associate an actual user with one of these sessions, take note of the process id (pid) of the process associated with the session.

Log into Oracle Financials with an appropriate user and navigate to the user monitor screen. Enter query by example mode and enter the PID in the ‘Oracle Process’ screen.

Execute the query.

This will allow you to identify the user and take further action as appropriate.

Herman Scheepers