To identify long running concurrent requests for more than 1 hour.

# To identify the long running concurrent requests for more than 1 hour
Mailid=<List of Mail ids>
<Set the Environment>
sqlplus -s ‘apps/<apps password><<EOF >> /usr/tmp/logli1.log
spool /usr/tmp/longrunco.log
set line 200
set pagesize 500
col PROGRAM_NAME format a30
col concreq format a8
col Username format a10
col opid format a4
col dbuser format a6
SELECT SUBSTR(LTRIM(req.request_id),1,15) concreq,
           fcp.USER_CONCURRENT_PROGRAM_NAME “Program_Name”,
                fu.user_name “Username”,
               round((sysdate – actual_start_date) * 24 ,2) “Running_Hrs”,
           SUBSTR(proc.os_process_id,1,15) clproc,
           SUBSTR(LTRIM(proc.oracle_process_id),1,15) opid,
           SUBSTR(vsess.username,1,10) dbuser,
           SUBSTR(vproc.spid,1,10) svrproc,
           vsess.sid sid,
           vsess.serial# serial#
    FROM   fnd_concurrent_requests req,
           fnd_concurrent_processes proc,
           fnd_lookups look,
           fnd_lookups look1,
           V$process vproc,
           V$session vsess,
           fnd_concurrent_programs_vl fcp,
        fnd_user fu
    WHERE req.controlling_manager = proc.concurrent_process_id(+)
    AND    req.status_code = look.lookup_code
    AND    look.lookup_type = ‘CP_STATUS_CODE’
    AND    req.phase_code = look1.lookup_code
    AND    look1.lookup_type = ‘CP_PHASE_CODE’
    AND    look1.meaning = ‘Running’
    and    req.CONCURRENT_PROGRAM_ID = fcp.CONCURRENT_PROGRAM_ID
    AND    proc.oracle_process_id = vproc.pid(+)
    AND    vproc.addr = vsess.paddr(+)
    AND    fu.user_id = req.requested_by
    AND    round((sysdate – actual_start_date) * 24) > 1;
spool off
quit;
EOF
echo `date` >> $ORACLE_HOME/admin/$CONTEXT_NAME/udump/longrun_concurrent.log
cat /usr/tmp/longrunco.log >> $ORACLE_HOME/admin/$CONTEXT_NAME/udump/longrun_concurrent.log
cat /usr/tmp/longrunco.log |mailx -s “Alert:Long Running Concurrent requests Check” $Mailid
rm -rf /usr/tmp/longrunco.log

Doyensys

A technology service company that provides innovative solutions around the oracle platform With Laser focus on customer delight as the primary success parameter.