Thursday, April 9, 2009

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;

====================================================

1 comment:

Unknown said...

thanks. I know another way of sql 2000 recovery of database, maybe you will be interested