To find the which sql is executing by the concurrent program in a schema

Run the below query first to get the SID and the program details.


SELECT   q.concurrent_queue_name qname, f.user_name, a.request_id "Req Id",
         DECODE (a.parent_request_id, -1, NULL, a.parent_request_id) "Parent",
         a.concurrent_program_id "Prg Id", a.phase_code, a.status_code,
         vs.inst_id, vs.sid, vs.serial# "Serial#", vp.spid,
         b.os_process_id apprsid,
           (  NVL (a.actual_completion_date, SYSDATE)
            - a.actual_start_date
           )
         * 1440
               "Time",
            c.concurrent_program_name
         || ' - '
         || c2.user_concurrent_program_name "Program"
    FROM applsys.fnd_concurrent_requests a,
         applsys.fnd_concurrent_processes b,
         applsys.fnd_concurrent_queues q,
         applsys.fnd_concurrent_programs_tl c2,
         applsys.fnd_concurrent_programs c,
         applsys.fnd_user f,
         gv$session vs,
         gv$process vp
   WHERE a.controlling_manager = b.concurrent_process_id
     AND a.concurrent_program_id = c.concurrent_program_id
     AND a.program_application_id = c.application_id
     AND c2.concurrent_program_id = c.concurrent_program_id
     AND f.user_name = :user_name
     AND c2.application_id = c.application_id
     AND a.phase_code IN ('I', 'P', 'R', 'T')
     AND a.status_code IN ('R')
     AND a.requested_by = f.user_id
     AND b.queue_application_id = q.application_id
     AND b.concurrent_queue_id = q.concurrent_queue_id
     AND vp.spid = a.oracle_process_id
     AND vs.paddr(+) = vp.addr
     AND vs.inst_id(+) = vp.inst_id
ORDER BY 12;

Take the SID of the concurrent program and give the SID to the below program

select sql_text,HASH_VALUE from gv$sqltext t,gv$session s

where t.ADDRESS = s.SQL_ADDRESS

and t.HASH_VALUE = s.SQL_HASH_VALUE

and s.sid = &sid

order by PIECE ;