SQL script to find requests running for more than 1 hour

Use the following script to find out requests that are running (currently) for more than one hour.

====================================================
-- --------------------------------------------------
-- Author : New Generation Consultants
-- Script to find long running requests
-- --------------------------------------------------
set lines 300
set trims on
set pages 60
set head on

ttit off
btit off
alter session set nls_Date_format='DD-MON-RRRR HH24:MI';
col acd format a23 Heading "End Time"
col cpn format a15 heading "Program"
col ucpn format a35 heading "Program Name"
col runtime format 9999999 heading "Runtime"
col asd format a23 Heading "Start Time"
SELECT f.request_id
, DECODE(p.concurrent_program_name,
'ALECDC',p.concurrent_program_name'['f.description']'
,p.concurrent_program_name) cpn
, pt.user_concurrent_program_name ucpn
, TRUNC(((sysdate-f.actual_start_date)/(1/24))*60) runtime
, f.actual_start_date asd
FROM apps.fnd_concurrent_programs p
, apps.fnd_concurrent_programs_tl pt
, apps.fnd_concurrent_requests f
WHERE f.phase_code = 'R'
and (sysdate-f.actual_start_date)*24*60 > 60
and f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV('Lang')
ORDER by f.actual_start_date desc;
====================================================

Downloading, Installing and running Remote Diagnostic Agent (RDA)

RDA can be downloaded as a bundle with Oracle Configuration manager or as a stand alone package from metalink at note id 314422.1.

- Download the zip/tar files from the note and change the name to 'rda.zip' or 'rda.tar' depending on what format you download.

- Choose or create a directory that is owned by the OS user who is also the owner of the Oracle installation.

- Copy the rda zip/tar file to the above directory and extract its contents there using one of the following commands depending on the format of the RDA download.

tar xvf rda.tar (or)
gunzip rda.tar.gz (or)
tar xvf rda.tar (or)
unzip rda.zip

- Make sure the rda.sh and rda.pl scripts have execute permissions. If they lack it, issue the following command

chmod +x rda.sh
chmod +x rda.pl

- Test if the rda scripts can be executed

for unix/linux - sh rda.sh -cv
for windows - rda.cmd -cv

these commands should not return any issues

- To run RDA, log in as the user who owns the Oracle installation and execute the following script

for Linux/Unix machines

rda.sh - Use this command if Perl is not available.

rda.pl - Use this command if Perl is available.

For windows,

rda.cmd Use this command if Perl is not available.

rda.pl Use this command if Perl is available in the path.


- To check availability of PERL, use the following command.

perl -v