Tuesday, October 30, 2012

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';

2 comments:

Oracle DBA support said...

It is very good blog.I am also looking for this from a long time.Many people like to visit here.I have seen many things over here.It is very good.

Oracle Consultancy

Balaganesh Gembali said...

Thank you

Autoconfig post 12C upgrade from 11g failed with "ORA-01804: failure to initialize timezone information"

Autoconfig post 12C upgrade from 11g failed with "ORA-01804: failure to initialize timezone information" Error DB Autoconfig ...