Apps DBA Scripts

1. Provide Concurrent Program Name, It will list out all concurrent requests sets names that has concurrent program in it?

SELECT DISTINCT user_request_set_name
  FROM FND_REQUEST_SETS_TL
 WHERE request_set_id IN
          (SELECT request_set_id
             FROM FND_REQUEST_SET_PROGRAMS
            WHERE concurrent_program_id =
                     (SELECT CONCURRENT_PROGRAM_ID
                        FROM fnd_concurrent_programs_tl
                       WHERE upper(USER_CONCURRENT_PROGRAM_NAME) = upper( '&Enter_Prog_name')));

2. Provide Concurrent Request Set Name, It will list out all the concurrent programs It has?

SELECT USER_CONCURRENT_PROGRAM_NAME
  FROM fnd_concurrent_programs_tl
 WHERE CONCURRENT_PROGRAM_ID IN
          (SELECT concurrent_program_id
             FROM FND_REQUEST_SET_PROGRAMS
            WHERE request_set_id =
                     (SELECT request_set_id
                        FROM FND_REQUEST_SETS_TL
                       WHERE upper(user_request_set_name) = upper('&Request_Set_Name')));

3. Find out Inactive Form sessions for X hours?


set lines 200 pages 200
col event for a30

select a.sid,a.serial#,a.action,a.status,a.event,round(a.last_call_Et/60/60) LSTCALL_Hrs, 
round(a.seconds_in_wait/60/60) SCNDINWAIT_Hrs,
       a.process, b.spid
  from v$session a, v$process b
 where a.action like 'FRM%'
   and a.paddr = b.addr
   and a.last_call_Et/60/60 > &&No_Of_Hours_Old order by 6,5;

4. Find out Application Names (Products) in Oracle Apps?

set lines 200

set pagesize 300

col APPLICATION_NAME for a70

select a.APPLICATION_NAME, b.APPLICATION_SHORT_NAME from apps.fnd_application b, apps.fnd_application_tl a  where a.APPLICATION_ID=b.APPLICATION_ID; 

5. Find out What are all concurrent programs are assigned to What are all concurrent Managers?


set lines 200 pages 300

col USER_CONCURRENT_QUEUE_NAME for a50
col CONCURRENT_PROGRAM_NAME for a50

break on USER_CONCURRENT_QUEUE_NAME skip 1;
SELECT C.USER_CONCURRENT_QUEUE_NAME,B.CONCURRENT_PROGRAM_NAME,A.INCLUDE_FLAG
FROM APPLSYS.FND_CONCURRENT_QUEUE_CONTENT A, APPLSYS.FND_CONCURRENT_PROGRAMS B, APPS.FND_CONCURRENT_QUEUES_VL C
WHERE type_id = b.concurrent_program_id and c.concurrent_queue_id = a.concurrent_queue_id order by C.USER_CONCURRENT_QUEUE_NAME;

6. Find out session details  of a concurrent Request?


set lines 200 pages 300
col USER_CONCURRENT_PROGRAM_NAME for a40

select USER_CONCURRENT_PROGRAM_NAME,c.request_id,s.sid,p.spid,s.process,s.osuser,s.username,s.program,s.status,logon_time,last_call_et
from v$session s, v$process p, apps.fnd_concurrent_requests c,apps.fnd_concurrent_programs_tl ct
where oracle_process_id=p.spid
and s.paddr=p.addr and
ct.concurrent_program_id=c.concurrent_program_id
and request_id=&creq_id
/

7. Find out All Concurrent Queue/Manager sizes/Processes?

set lines 200 pages 300
select a.concurrent_queue_name,b.min_processes,b.max_processes from apps.fnd_concurrent_queues a,apps.fnd_concurrent_queue_size b where a.concurrent_queue_id=b.concurrent_queue_id;

8. Find out Responsibility name from a Concurrent program?

set lines 200 pages 300
SELECT DISTINCT
  FCPL.USER_CONCURRENT_PROGRAM_NAME
, FCP.CONCURRENT_PROGRAM_NAME
, FAPP.APPLICATION_NAME
, FRG.REQUEST_GROUP_NAME
, FNRTL.RESPONSIBILITY_NAME
FROM
  APPS.FND_REQUEST_GROUPS FRG
, APPS.FND_APPLICATION_TL FAPP
, APPS.FND_REQUEST_GROUP_UNITS FRGU
, APPS.FND_CONCURRENT_PROGRAMS FCP
, APPS.FND_CONCURRENT_PROGRAMS_TL FCPL
, APPS.FND_RESPONSIBILITY FNR
, APPS.FND_RESPONSIBILITY_TL FNRTL
WHERE FRG.APPLICATION_ID=fapp.APPLICATION_ID
AND FRG.APPLICATION_ID = FRGU.APPLICATION_ID
AND FRG.REQUEST_GROUP_ID = FRGU.REQUEST_GROUP_ID
AND FRG.REQUEST_GROUP_ID = FNR.REQUEST_GROUP_ID
AND FRG.APPLICATION_ID = FNR.APPLICATION_ID
AND FNR.RESPONSIBILITY_ID = FNRTL.RESPONSIBILITY_ID
AND FRGU.REQUEST_UNIT_ID = FCP.CONCURRENT_PROGRAM_ID
AND FRGU.UNIT_APPLICATION_ID = FCP.APPLICATION_ID
AND FCP.CONCURRENT_PROGRAM_ID = FCPL.CONCURRENT_PROGRAM_ID
AND FCPL.USER_CONCURRENT_PROGRAM_NAME LIKE '%&Conc_Prog_name%'
AND FNRTL.LANGUAGE = 'US'
AND FAPP.LANGUAGE = 'US';

8. Find out Currently running concurrent requests with OSPID,SID and Serial# etc?

SELECT a.request_id, d.sid, d.serial# , c.SPID
    FROM apps.fnd_concurrent_requests a,
    apps.fnd_concurrent_processes b,
    v$process c,
    v$session d
    WHERE a.controlling_manager = b.concurrent_process_id
    AND c.pid = b.oracle_process_id
    AND b.session_id=d.audsid
    AND a.phase_code = 'R' and status_code='R';