To Find Session Details

To find info about OS Process ID

top command will give you the PID info

ps -ef | grep PID

(It will give you the info. when it was created and which module it is)

select sid, serial#, program, osuser, client_identifier, module, machine, action from v$session 

where process='PID';

To check the Blocking Sessions:

select distinct decode(vlock.request,0,'Holder','Waiter') Type, vses.CLIENT_IDENTIFIER, vlock.block, vlock.request, vses.sid, vses.serial#, vses.status, round(vses.last_call_et/60) mins, vses.event, vses.module, vses.sql_id, vses.machine from v$session vses, v$lock vlock where vlock.sid=vses.sid and (vlock.id1, vlock.id2, vlock.type) in (SELECT id1, id2, type FROM v$lock where request>0) order by 1;

To Check the Concurrent Request Info

ps -ef | grep sqlplus  -- To know PID

oracle    12345   09876   0  22:12   pts/2    00:00:00   sqlplus

select sid, serial#, event, sql_id, blocking_session, blocking_instance from gv$session

where process='12345';

select inst_id, sql_id, program, module, action, last_call_et, status, event, blocking_session from gv$session where sid=SID and inst_id=ID;

--We can see the program, module and action

select audsid from gv$session where sid=SID and serial#=SerialNO;

select request_id, phase_code, status_code, actual_start_data, sysdate from fnd_concurrent_requests

where oracle_session_id=AUDSID;