long running concurrent request

 @RR to check the long run concurrent:

set pages 1000 lines 1000 pagesize 1000;
col program for a80;
select a.request_id ,decode(a.phase_code,'R','Running','Normal') Phase,
to_char(a.actual_start_date,'DD-MON-RRRR HH24:MI:SS')
Start_time , substr(b.user_concurrent_program_name,1,80) Program
from apps.fnd_concurrent_requests a, apps.fnd_concurrent_programs_tl b
where a.concurrent_program_id = b.concurrent_program_id and
a.status_code = 'R'
order by Start_time;

@RR_SID to find the sid and serial number

set lines 200;
set pages 300;
col program for a50;
SELECT a.request_id, substr(e.user_concurrent_program_name,1,50) Program,d.sid, d.serial# ,d.status,to_char(d.logon_time,'DD-MON-YY HH24:MI:SS')logon, round(d.last_call_et/60) LCT
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,v$session d,
apps.fnd_concurrent_programs_tl e
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id=e.concurrent_program_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.phase_code = 'R'
--and d.sql_id='fnpyvpk41nd5s'
order by 7 desc;

@RR.det to check the parameter of concurrent;

set lines 200;
set pages 1000;
col program for a40;
col user_name for a20;
select a.request_id ,decode(a.phase_code,'R','Running','Normal') Phase,
to_char(a.actual_start_date,'DD-MON-RRRR HH24:MI:SS')
Start_time , substr(b.user_concurrent_program_name,1,40) Program, substr(a.argument_text,1,60) argument, c.user_name
from apps.fnd_concurrent_requests a, apps.fnd_concurrent_programs_tl b, apps.fnd_user c
where a.concurrent_program_id = b.concurrent_program_id
and a.status_code = 'R'
and a.requested_by=c.user_id
order by Start_time;

@prerun to check the parameter of concurrent completed sucessfully 

set lines 200;
set pages 1000;
col user_name for a20;
col argument_text for a60;
SELECT a.request_id, To_Char(a.actual_start_date,'DD-MON HH24:MI') START_DATE,
  To_Char(a.actual_completion_date,'DD-MON HH24:MI') END_DATE, round(((a.actual_completion_date-a.actual_start_date)*24*60*60/60),2) AS Process_time,
 a.phase_code P,a.status_code S, a.argument_text,d.user_name
FROM   apps.fnd_concurrent_requests a,
            apps.fnd_concurrent_programs b ,
            apps.FND_CONCURRENT_PROGRAMS_TL c,
            apps.fnd_user d
WHERE       a.concurrent_program_id= b.concurrent_program_id AND
            b.concurrent_program_id=c.concurrent_program_id AND
            a.requested_by =d.user_id AND
       trunc(a.actual_completion_date) >= trunc(sysdate-7) AND
c.USER_CONCURRENT_PROGRAM_NAME like '%&Concurrent_program_name%'
    order by start_date desc;

@sess_io to check the session block changing

select * from v$sess_io where sid=&sid;

lock on table:

select c.owner,c.object_name,c.object_type,c.status,b.sid,b.serial#,b.status,b.osuser,b.machine,a.process,a.locked_mode
from
v$locked_object a ,
v$session b,
dba_objects c
where
b.sid = a.session_id
and
a.object_id = c.object_id;

block Session 

SELECT
  blocking_session "BLOCKING_SESSION",
  sid "BLOCKED_SESSION",
  serial# "BLOCKED_SERIAL#",
  seconds_in_wait/60 "WAIT_TIME(MINUTES)"
FROM v$session
WHERE blocking_session is not NULL
ORDER BY blocking_session;

Block session with details:

  select a.process,a.sql_id,a.sid,a.serial#,a.blocking_session,a.final_blocking_session,to_char(a.logon_time,'DD-MON-YY HH24:MI:SS')logon,round(a.last_call_et/60) LCT,a.username,a.status,a.machine,a.program,a.module,a.action,a.event,a.client_identifier,sql_text
    from gv$sqlarea sqlarea, gv$session a
    where a.sql_hash_value= sqlarea.hash_value(+)
    and a.sql_address= sqlarea.address(+)
    and a.username is not null
    ---and a.status = 'ACTIVE'
    --and a.module like '%SQL De%'
    and a.sid in ('1050','')
    --and a.client_identifier='JDESAI'
    --and sql_text='begin FND_CP_OPP_REQ.postprocess (:reqid, :groupid, :flag, :errmsg); end;'
    --and a.module ='e:MSC:cp:po/POXRSR'
    and a.type='USER'
    order by 8 desc;