APPS DBA quick scripts


Check the profile option which has been changed

 set pagesize 9999
set linesize 120
set verify off
col "Profile Option" for a25
col "Option Level" for a13
col "set for" for a20
col "Value" for a20
col "Set On" for a11
col "Blame" for a20
PROMPT Enter date value in form DD-MON-YYYY for check_since
   select tl.user_profile_option_name "Profile Option"
        , decode( val.level_id
                , 10001, 'Site'
                , 10002, 'Application'
                , 10003, 'Responsibility'
                , 10004, 'User'
                , 10005, 'Server'
                , 10006, 'Organization'
                , 10007, 'Server+Resp'
                , 'No idea, boss') "Option Level"
        , decode( val.level_id
                , 10001
                , 'EVERYWHERE!'
                , 10002
                , (select application_name
                     from fnd_application_tl
                    where application_id = val.level_value)
                , 10003
                , (select responsibility_name
                     from fnd_responsibility_tl
                    where responsibility_id = val.level_value
                      and application_id = val.level_value_application_id)
                , 10004
                , (select user_name
                     from fnd_user
                    where user_id = val.level_value)
                , 10005
                , (select host || '.' || domain
                     from fnd_nodes
                    where node_id = val.level_value)
                , 10006
                , (select name
                     from hr_all_organization_units
                    where organization_id = val.level_value)
                , 10007
                , 'Look it up' --per specification El-Ay-Zed-why
                , '''Tis a mystery') "Set for"
        , val.profile_option_value "Value"
        , val.last_update_date "Set on"
        , usr.user_name "Set By"
     from fnd_profile_options opt,
          fnd_profile_option_values val,
          fnd_profile_options_tl tl,
          fnd_user usr
    where opt.profile_option_id = val.profile_option_id
      and opt.profile_option_name = tl.profile_option_name
      and regexp_like( tl.user_profile_option_name
                     , '(trace|log|debug|audit|diag|sql)'
                     , 'i'
                     )
      and not(regexp_like( tl.user_profile_option_name
                         , '(catalog|file|login|utilities)'
                         , 'i'
                         )
             )
      and usr.user_id = val.last_updated_by
      and usr.user_name  not in ( 'AUTOINSTALL'
                                , 'INITIAL SETUP'
                                , 'ANONYMOUS')
      and val.last_update_date > '&check_since'
    order by val.last_update_date desc

;

 Find the most recent changes to E-Business Suite profile options
set pagesize 9999
set linesize 80
set verify off
col "Profile Option" for a15
col "Option Level" for a13
col "Value" for a15
col "Set On" for a9
col "Scoldee" for a15
select * from
(
select tl.user_profile_option_name "Profile Option",
decode(val.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
10005, 'Server',
10006, 'Organization',
10007, 'Server+Resp',
'No idea, boss') "Option Level",
val.profile_option_value "Value",
val.last_update_date "Set on",
usr.user_name "Scoldee"
from fnd_profile_options opt,
fnd_profile_option_values val,
fnd_profile_options_tl tl,
fnd_user usr
where opt.profile_option_id = val.profile_option_id
and opt.profile_option_name = tl.profile_option_name
and usr.user_id = val.last_updated_by
order by val.last_update_date desc
)
where rownum <= &number_of_items
;
exit;



script to check same patch applied twice

 SELECT DISTINCT RPAD(a.bug_number,11)||RPAD(e.patch_name,11)|| RPAD(TRUNC(c.end_date),12)|| RPAD(b.applied_flag,4)
FROM
ad_bugs a,
ad_patch_run_bugs b,
ad_patch_runs c,
ad_patch_drivers d ,
ad_applied_patches e
WHERE a.bug_id = b.bug_id AND
b.patch_run_id = c.patch_run_id AND
c.patch_driver_id = d.patch_driver_id AND
d.applied_patch_id = e.applied_patch_id AND
a.bug_number LIKE &patch_number
ORDER BY 1 DESC


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

select * from ad_bugs
where trackable_entity_abbr = 'po'

and last_update_date >= '01-JAN-15';

**********************************************************************

To list down the processing details of Concurrent managers

SELECT
TO_CHAR (actual_start_date, 'DD-MON-YY : HH24') "Queue Time",
fcqtl.user_concurrent_queue_name "Concurrent Manager",
fcptl.user_concurrent_program_name "Program",
ROUND
( SUM (GREATEST (actual_completion_date - actual_start_date, 0))
* 60
* 24,
2
) "Total Duration (min)",
ROUND
( AVG (GREATEST (actual_completion_date - actual_start_date, 0))
* 60
* 24,
2
) "Avg Duration (min)",
ROUND
( MIN (GREATEST (actual_completion_date - actual_start_date, 0))
* 60
* 24,
2
) "Min Duration (min)",
ROUND
( MAX (GREATEST (actual_completion_date - actual_start_date, 0))
* 60
* 24,
2
) "Max Duration (min)",
COUNT (*) "Times Run", fcq.target_processes "Total Processes"
FROM fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcptl,
fnd_concurrent_processes fcproc,
fnd_concurrent_queues_tl fcqtl,
fnd_concurrent_queues fcq,
fnd_concurrent_requests fcr
WHERE fcr.phase_code = 'C'
AND fcr.actual_completion_date IS NOT NULL
AND actual_start_date IS NOT NULL
AND fcq.concurrent_queue_id = fcproc.concurrent_queue_id
AND fcq.application_id = fcproc.queue_application_id
AND fcq.manager_type = 1
AND fcr.controlling_manager = fcproc.concurrent_process_id
AND fcr.program_application_id = fcp.application_id
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcp.concurrent_program_name NOT IN
('ACTIVATE', 'ABORT', 'DEACTIVATE', 'VERIFY')
AND fcr.concurrent_program_id = fcptl.concurrent_program_id
AND fcr.program_application_id = fcptl.application_id
AND fcptl.LANGUAGE = 'US'
AND fcproc.queue_application_id = fcqtl.application_id
AND fcproc.concurrent_queue_id = fcqtl.concurrent_queue_id
AND fcqtl.LANGUAGE = 'US'
GROUP BY TO_CHAR (actual_start_date, 'DD-MON-YY : HH24'),
fcqtl.user_concurrent_queue_name,
fcptl.user_concurrent_program_name,
fcq.target_processes
ORDER BY "Queue Time" ASC,
"Concurrent Manager" ASC,
"Times Run" DESC,
"Max Duration (min)" DESC,
"Total Duration (min)" DESC
**********************************************************************

Concurrent program details for a particular day

 select * from apps.fnd_concurrent_requests where trunc(request_date) =trunc(sysdate -2);

**********************************************************************

Concurrent request details and managers which has handled them

SELECT
a.request_id,
a.user_concurrent_program_name,
y.USER_CONCURRENT_QUEUE_NAME Processing_Manager_Name,
TO_CHAR(a.requested_start_date,'dd-Mon-yyyy :hh24:mi') Requested_start_date,
TO_CHAR(a.actual_start_date,'dd-Mon-yyyy :hh24:mi') Actual_start_date,
TO_CHAR(a.actual_completion_date,'dd-Mon-yyyy :hh24:mi') completion_date,
FLOOR((a.ACTUAL_COMPLETION_DATE- a.ACTUAL_START_DATE)*24) "in Hours",
round((((a.ACTUAL_COMPLETION_DATE- a.ACTUAL_START_DATE)*24)-(FLOOR((a.ACTUAL_COMPLETION_DATE- a.ACTUAL_START_DATE)*24)))*60) "In_Min",
FLOOR((a.ACTUAL_start_DATE- a.requested_START_DATE)*24)*60 "Start Delay By Mins",
b.logfile_node_name as "HOSTNAME",SUBSTR(a.requestor,1,30) as "REQUESTOR" ,
trim(b.argument_text) as "PARAMETERS"
FROM fnd_conc_req_summary_v a,fnd_concurrent_requests b,
fnd_concurrent_processes x, fnd_concurrent_queues_vl y
where b.request_id=a.request_id
and x.CONCURRENT_QUEUE_ID = y.CONCURRENT_QUEUE_ID
and x.CONCURRENT_PROCESS_ID = a.controlling_manager
and a.actual_start_date < sysdate
order by a.actual_start_date
**********************************************************************

Find the trace file for concurrent program

SELECT 'Request id: '||request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name: '||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'Prog. Name: '||prog.user_concurrent_program_name,
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running') ||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#, 'Module : '||ses.module
from
fnd_concurrent_requests req,
v$session ses,
v$process proc,
v$parameter dest,
v$parameter dbnm,
fnd_concurrent_programs_vl prog,
fnd_executables execname where req.request_id = &request
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id;
**********************************************************************
get sid from spid

 SELECT a.sid, b.spid, a.username, a.osuser
  FROM v$session a, v$process b
 WHERE a.paddr = b.addr(+)
 and b.spid=;
**********************************************************************
OS command to kill CM process
ps -ef |grep FNDLIBR | grep -v grep | awk '{print $2}' | xargs kill -9
**********************************************************************

Script to check the running concurrent programs


 SELECT DISTINCT c.USER_CONCURRENT_PROGRAM_NAME,round(((sysdate-a.actual_start_date)*24*60*60/60),2) AS Process_time,
 a.request_id,a.parent_request_id,a.request_date,a.actual_start_date,a.actual_completion_date,(a.actual_completion_date-a.request_date)*24*60*60 AS end_to_end,
 (a.actual_start_date-a.request_date)*24*60*60 AS lag_time,d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
FROM   apps.fnd_concurrent_requests a,apps.fnd_concurrent_programs b,apps.FND_CONCURRENT_PROGRAMS_TL c,apps.fnd_user d
WHERE  a.concurrent_program_id=b.concurrent_program_id AND b.concurrent_program_id=c.concurrent_program_id AND
a.requested_by=d.user_id AND status_code='R' order by Process_time desc;










**********************************************************************


set echo off
set feedback off
set linesize 97
set verify off
col request_id format 9999999999    heading "Request ID"
     col exec_time format 999999999 heading "Exec Time|(Minutes)"
    col start_date format a10       heading "Start Date"
     col conc_prog format a20       heading "Conc Program Name"
col user_conc_prog format a40 trunc heading "User Program Name"
spool long_running_cr.lst
SELECT
   fcr.request_id request_id,
   TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) exec_time,
   fcr.actual_start_date start_date,
   fcp.concurrent_program_name conc_prog,
   fcpt.user_concurrent_program_name user_conc_prog
FROM
  fnd_concurrent_programs fcp,
  fnd_concurrent_programs_tl fcpt,
  fnd_concurrent_requests fcr
WHERE
   TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) > NVL('&min',45)
and
   fcr.concurrent_program_id = fcp.concurrent_program_id
and
   fcr.program_application_id = fcp.application_id
and
   fcr.concurrent_program_id = fcpt.concurrent_program_id
and
   fcr.program_application_id = fcpt.application_id
and
   fcpt.language = USERENV('Lang')
ORDER BY
   TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) desc;
         
spool off

**********************************************************************



set linesize 155
set pages 999

column seconds_in_wait              Heading "Sec/Wait"            FORMAT A8
column spid                         Heading "   S/PID"            FORMAT A8
column process                      Heading "   C/PID"            FORMAT A8
column sid                          Heading "Orcl/SID"            FORMAT A8
column serial                       Heading "Orcl/Ser"            FORMAT A8
column user_name                    Heading "User Name"           FORMAT A11
column status                       Heading "Status"              FORMAT A11
column request_id                   Heading " Request ID"         FORMAT A11
column concurrent_program_name      Heading "Conc Prog Name"      FORMAT A16
column user_concurrent_program_name Heading "User Conc Prog Name" FORMAT A21
column requested_start_Date         HEADING "Strt Date/Time"      FORMAT A15
column running_minutes              HEADING "Mins. Running"       FORMAT A13

select
to_char(w.seconds_in_wait,'9999999') seconds_in_wait,
to_char(p.spid,'9999999') spid,
to_char(s.process,'9999999') process,
to_char(s.sid,'9999999') sid,
to_char(s.serial#,'9999999') serial,
substr(u.user_name,1,10)  user_name,
substr(s.status,1,10) status,
to_char(Request_id,'9999999999') request_id,
substr(concurrent_program_name,1,15) concurrent_program_name,
substr(user_concurrent_program_name,1,20) user_concurrent_program_name,
to_char(requested_start_Date,'DD-MON-RR HH:MI') requested_start_Date,
to_char(round((sysdate- requested_start_date)*1440, 2),'9,999,999.99') running_minutes
FROM FND_CONCURRENT_WORKER_REQUESTS, fnd_user u, v$session s, v$process p, v$session_wait w
WHERE (Phase_Code='R')and hold_flag != 'Y'and Requested_Start_Date <= SYSDATE
AND ('' IS NULL OR ('' = 'B' AND PHASE_CODE = 'R' AND STATUS_CODE IN ('I', 'Q')))and '1' in (0,1,4)
and requested_by=u.user_id and s.paddr=p.addr and s.sid=w.sid and oracle_process_id = p.spid
and oracle_session_id = s.audsid
order by requested_start_date;

**********************************************************************
To find the OPP logs
SELECT fcpa.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name
  FROM fnd_conc_pp_actions fcpa, fnd_concurrent_processes fcp
 WHERE fcpa.processor_id = fcp.concurrent_process_id
   AND fcpa.action_type = 6
   AND fcpa.concurrent_request_id = :P_REQUEST_ID;

**********************************************************************
To know conc program name from request id
Select * from FND_CONC_REQ_SUMMARY_V where request_id = '4716010';
**********************************************************************

 To know the child request id from parent id
#Find child request id from parent:

  SELECT                                 /*+ ORDERED USE_NL(x fcr fcp fcptl)*/
        fcr.request_id "Request ID",
         fcptl.user_concurrent_program_name "Program Name",
         fcr.phase_code,
         fcr.status_code,
         --     to_char(fcr.request_date,'DD-MON-YYYY HH24:MI:SS') "Submitted",
         --     (fcr.actual_start_date - fcr.request_date)*1440 "Delay",
         TO_CHAR (fcr.actual_start_date, 'DD-MON-YYYY HH24:MI:SS') "Start Time",
         TO_CHAR (fcr.actual_completion_date, 'DD-MON-YYYY HH24:MI:SS')
            "End Time",
         (fcr.actual_completion_date - fcr.actual_start_date) * 1440 "Elapsed",
         fcr.oracle_process_id "Trace ID"
    FROM (    SELECT            /*+ index (fcr1 fnd_concurrent_requests_n3) */
                    fcr1.request_id
                FROM apps.fnd_concurrent_requests fcr1
               WHERE 1 = 1
          START WITH fcr1.request_id = 5187580
          CONNECT BY PRIOR fcr1.request_id = fcr1.parent_request_id) x,
         apps.fnd_concurrent_requests fcr,
         apps.fnd_concurrent_programs fcp,
         apps.fnd_concurrent_programs_tl fcptl
   WHERE     fcr.request_id = x.request_id
         AND fcr.concurrent_program_id = fcp.concurrent_program_id
         AND fcr.program_application_id = fcp.application_id
         AND fcp.application_id = fcptl.application_id
         AND fcp.concurrent_program_id = fcptl.concurrent_program_id
         AND fcptl.LANGUAGE = 'US'
ORDER BY 1;

**********************************************************************
to know the concurrent program name from the module name
select fcpl.user_concurrent_program_name
     , fcp.concurrent_program_name
     , fcp.concurrent_program_id
     , fav.application_short_name
     , fav.application_name
     , fav.application_id
     , fdfcuv.end_user_column_name
     , fdfcuv.form_left_prompt prompt
     , fdfcuv.enabled_flag
     , fdfcuv.required_flag
     , fdfcuv.display_flag
from   apps.fnd_concurrent_programs fcp
     , apps.fnd_concurrent_programs_tl fcpl
     , apps.fnd_descr_flex_col_usage_vl fdfcuv
     , apps.fnd_application_vl fav
where  fcp.concurrent_program_id = fcpl.concurrent_program_id
and    fcpl.user_concurrent_program_name = :conc_prg_name
and    fav.application_id=fcp.application_id
and    fcpl.language = 'US'
**********************************************************************

To know the sid of a concurrent request
SELECT sid 
FROM v$session
WHERE paddr LIKE 
(SELECT addr 
FROM v$process
WHERE spid = 
(SELECT oracle_process_id 
FROM fnd_concurrent_requests
WHERE request_id = TO_NUMBER(<your request id>) 
) 
);

**********************************************************************





SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$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.request_id =&req_id
AND a.phase_code = 'R';




Once sid is known we can know the sql id.

select sid,sql_id,action,module from v$session where sid=

**********************************************************************
Undo tablespace usage

SELECT d.tablespace_name, round(((NVL(f.bytes,0) + (a.maxbytes - a.bytes))/1048576+ u.exp_space),2)
as max_free_mb, round(((a.bytes - (NVL(f.bytes,0)+ (1024*1024*u.exp_space)))*100/a.maxbytes),2)
used_pct FROM   sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes,
sum(greatest(maxbytes,bytes)) maxbytes from sys.dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes from sys.dba_free_space group by tablespace_name) f ,
(select tablespace_name , sum(blocks)*8/(1024)  exp_space from
dba_undo_extents where status NOT IN ('ACTIVE','UNEXPIRED')  group by  tablespace_name) u
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+)
AND d.tablespace_name=u.tablespace_name  AND d.contents = 'UNDO' AND u.tablespace_name = (select UPPER(value)
from v$parameter where name = 'undo_tablespace');
**********************************************************************

 Temporary tablespace usage

select name from v$database;

select file_name , TABLESPACE_NAME from DBA_TEMP_FILES;


SELECT   A.tablespace_name, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM(A.used_blocks * D.block_size)/ 1024/ 1024 mb_free
FROM     v$sort_segment A,
(
SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM     v$tablespace B, v$tempfile C
WHERE    B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;


SELECT   S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM     v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE    T.session_addr = S.saddr
AND      S.paddr = P.addr
AND      T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY mb_used;

SELECT  S.sid || ',' || S.serial# sid_serial, S.username, Q.hash_value, Q.sql_text,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace
FROM    v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE   T.session_addr = S.saddr
AND     T.sqladdr = Q.address
AND     T.tablespace = TBS.tablespace_name
ORDER BY mb_used;
**********************************************************************

 Tablespace monitoring

Select t.tablespace, t.totalspace as " Totalspace(MB)", round((t.totalspace-fs.freespace),2) as "Used Space(MB)", fs.freespace as "Freespace(MB)", round(((t.totalspace-fs.freespace)/t.totalspace)*100,2) as "% Used", round((fs.freespace/t.totalspace)*100,2) as "% Free" from (select round(sum(d.bytes)/(1024*1024)) as totalspace, d.tablespace_name tablespace from dba_data_files d group by d.tablespace_name) t, (select round(sum(f.bytes)/(1024*1024)) as freespace, f.tablespace_name tablespace from dba_free_space f group by f.tablespace_name) fs where t.tablespace=fs.tablespace order by t.tablespace;
**********************************************************************Calculate Table size

SELECT owner,
segment_name,
segment_type,
tablespace_name,
bytes/1048576 MB,
initial_extent,
next_extent,
extents,
pct_increase
FROM
DBA_SEGMENTS
WHERE
OWNER = 'table owner' AND
SEGMENT_NAME = 'table name' AND
SEGMENT_TYPE = 'TABLE'
/

************percentage completion of gather stat program*************
select

  total_tables,

  round((done_tables-total_tables)*100,2) "%tables|done",

  round((done_ROWS-total_rows)*100,2) "%rows|done"

from (

  select

    count(*) over() total_tables,

    sum(num_rows) over() total_rows,

    sum(decode(sign(sysdate-last_analyzed-1/2),-1,num_rows)) over() done_tables,

    sum(decode(sign(sysdate-last_analyzed-1/2),-1,num_rows)) over() done_rows

  from all_tables

  where owner=upper('schema name'));


**************check the percentage completion of long ops************

SELECT X.*, TO_CHAR(SYSDATE, 'HH24:MIS') TIMESTAMP
  FROM (select sid, serial#, opname, sofar, totalwork,
    round(sofar/totalwork*100,2) "% Complete" from v$session_longops) X
  WHERE "% Complete" < 100 and totalwork > 0;
**********************************************************************


1.How to Determine Which Manager Ran a Specific Concurrent Request?

 col USER_CONCURRENT_QUEUE_NAME for a100
select b.USER_CONCURRENT_QUEUE_NAME from fnd_concurrent_processes a,
fnd_concurrent_queues_vl b, fnd_concurrent_requests c
where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID
and a.CONCURRENT_PROCESS_ID = c.controlling_manager
and c.request_id = '&conc_reqid';
=========================================================================
Long Running Concurrent Request In Oracle :
Looking on how to check long running concurrent request in Oracle Apps 11i or R12? Here’s the overview of the SQL query script to detect the session information of each program.

 First you need to get the listing of running concurrent request in Oracle Apps 11i or R12. You can use the SQL query script as below to obtain the list of running request.
SELECT a.request_id
,a.oracle_process_id "SPID"
,frt.responsibility_name
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name
,a.description
,a.ARGUMENT_TEXT
,b.node_name
,b.db_instance
,a.logfile_name
,a.logfile_node_name
,a.outfile_name
,q.concurrent_queue_name
,a.phase_code,a.status_code, a.completion_text
, actual_start_date
, actual_completion_date
, fu.user_name
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins
,(SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440 avg_run_time
FROM APPLSYS.fnd_Concurrent_requests a2,
APPLSYS.fnd_concurrent_programs c2
WHERE c2.concurrent_program_id = c.concurrent_program_id
AND a2.concurrent_program_id = c2.concurrent_program_id
AND a2.program_application_id = c2.application_id
AND a2.phase_code || '' = 'C') avg_mins
,round((actual_completion_date - requested_start_date),2) * 24 duration_in_hours
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
,apps.fnd_user fu
,apps.FND_RESPONSIBILITY_TL frt
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND a.phase_code = 'R'
AND a.status_code = 'R'
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND a.requested_by = fu.user_id
AND a.responsibility_id = frt.responsibility_id
ORDER BY a.actual_start_date DESC
You can see the request id and other relevant information from the result.

 Based on the SPID associated to each running request, query the v$session or v$session_longops table to see what is the request id doing in the backend.

SELECT b.sid, b.serial#, a.spid, b.program, b.osuser, b.machine,
b.TYPE, b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time
FROM v$process a, v$session b, v$sqltext c
WHERE a.addr=b.paddr
AND b.sql_hash_value = c.hash_value
AND b.STATUS = 'ACTIVE'
AND a.spid = '11696'
ORDER BY a.spid, c.piece
Replace v$session with gv$session if the database is running on RAC environment. Enable or set trace if you wish to know more details on the session. 

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

 2.Concurrent manager status for a given sid?

 col MODULE for a20
col OSUSER for a10
col USERNAME for a10
set num 10
col MACHINE for a20
set lines 200
col SCHEMANAME for a10
select s.sid,s.serial#,p.spid os_pid,s.status, s.osuser,s.username, s.MACHINE,s.MODULE,
s.SCHEMANAME,
s.action from gv$session s, gv$process p WHERE s.paddr = p.addr and s.sid = '&oracle_sid';
3. Find out request id from Oracle_Process Id:

 select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_Id from apps.fnd_concurrent_requests where
ORACLE_PROCESS_ID='&a';

 4.To find sid,serial# for a given concurrent request id?

 set lines 200
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$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.request_id = &Request_ID
AND a.phase_code = 'R';
5.To find concurrent program name,phase code,status code for a given request id?
SELECT request_id, user_concurrent_program_name, DECODE(phase_code,'C','Completed',phase_code)
phase_code, DECODE(status_code,'D', 'Cancelled' ,
'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X',
'Terminated', 'C', 'Normal', status_code) status_code, to_char(actual_start_date,'dd-mon-
yy:hh24:mi:ss') Start_Date, to_char(actual_completion_date,'dd-mon-yy:hh24:mi:ss'),
completion_text FROM apps.fnd_conc_req_summary_v WHERE request_id = '&req_id' ORDER BY 6 DESC;

 6.To find the sql query for a given concurrent request sid?

 select sid,sql_text from gv$session ses, gv$sqlarea sql where
ses.sql_hash_value = sql.hash_value(+) and ses.sql_address = sql.address(+) and
ses.sid='&oracle_sid'
/

 7. To find child requests

 set lines 200
col USER_CONCURRENT_PROGRAM_NAME for a40
col PHASE_CODE for a10
col STATUS_CODE for a10
col COMPLETION_TEXT for a20
SELECT sum.request_id,req.PARENT_REQUEST_ID,sum.user_concurrent_program_name, DECODE
(sum.phase_code,'C','Completed',sum.phase_code) phase_code, DECODE(sum.status_code,'D',
'Cancelled' ,
'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X',
'Terminated', 'C', 'Normal', sum.status_code) status_code, sum.actual_start_date,
sum.actual_completion_date, sum.completion_text FROM apps.fnd_conc_req_summary_v sum,
apps.fnd_concurrent_requests req where req.request_id=sum.request_id and req.PARENT_REQUEST_ID =
'&parent_concurrent_request_id';
8. Cancelling Concurrent request :
update fnd_concurrent_requests
set status_code='D', phase_code='C'
where request_id=&req_id;

 9. Kill sessions program wise

 select 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' immediate;' from v$session where
MODULE like '';
10 .Concurrent Request running by SID

 SELECT a.request_id,
d.sid as Oracle_SID,
d.serial#,
d.osuser,
d.process,
c.SPID as OS_Process_ID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND d.sid = &SID;

 11. Find out request id from Oracle_Process Id:

 select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_Id from fnd_concurrent_requests where
ORACLE_PROCESS_ID='&a';
12. Oracle Concurrent Request Error Script (requests which were error ed out)
SELECT a.request_id "Req Id"
,a.phase_code,a.status_code
, actual_start_date
, actual_completion_date
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name "program"
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND a.status_code = 'E'
AND a.phase_code = 'C'
AND actual_start_date > sysdate - 2
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND ctl.LANGUAGE = 'US'
ORDER BY 5 DESC;
13. Request submitted by User

 SELECT
user_concurrent_program_name,
request_date,
request_id,
phase_code,
status_code
FROM
fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcp,
fnd_responsibility_tl fr,
fnd_user fu
WHERE
fcr.CONCURRENT_PROGRAM_ID = fcp.concurrent_program_id
and fcr.responsibility_id = fr.responsibility_id
and fcr.requested_by = fu.user_id
and user_name = '&user'
AND actual_start_date > sysdate - 1
ORDER BY REQUEST_DATE Asc;

14.Concurrent Program enable with trace

 col User_Program_Name for a40
col Last_Updated_By for a30
col DESCRIPTION for a30
SELECT A.CONCURRENT_PROGRAM_NAME "Program_Name",
SUBSTR(A.USER_CONCURRENT_PROGRAM_NAME,1,40) "User_Program_Name",
SUBSTR(B.USER_NAME,1,15) "Last_Updated_By",
SUBSTR(B.DESCRIPTION,1,25) DESCRIPTION
FROM APPS.FND_CONCURRENT_PROGRAMS_VL A, APPLSYS.FND_USER B
WHERE A.ENABLE_TRACE='Y'
AND A.LAST_UPDATED_BY=B.USER_ID

  15.Checking which manager is going to execute a program

 The below query identifies the manager which will be executing a given program. This query is based on the specialization rules set for the managers.
SELECT user_concurrent_program_name, user_concurrent_queue_name 
FROM apps.fnd_concurrent_programs_tl cp, 
apps.fnd_concurrent_queue_content cqc, 
apps.fnd_concurrent_queues_tl cq 
WHERE cqc.type_application_id(+) = cp.application_id 
AND cqc.type_id(+) = cp.concurrent_program_id 
AND cqc.type_code(+) = 'P' 
AND cqc.include_flag(+) = 'I' 
AND cp.LANGUAGE = 'US' 
AND cp.user_concurrent_program_name = '&USER_CONCURRENT_PROGRAM_NAME' AND NVL (cqc.concurrent_queue_id, 0) = cq.concurrent_queue_id 
AND NVL (cqc.queue_application_id, 0) = cq.application_id 
AND cq.LANGUAGE = 'US'


 16.To see all the pending / Running requests per each manager wise 
SELECT request_id, phase_code, status_code, user_name, 
user_concurrent_queue_name 
FROM apps.fnd_concurrent_worker_requests cwr, 
apps.fnd_concurrent_queues_tl cq, 
apps.fnd_user fu 
WHERE (cwr.phase_code = 'P' OR cwr.phase_code = 'R') 
AND cwr.hold_flag != 'Y' 
AND cwr.requested_start_date <= SYSDATE 
AND cwr.concurrent_queue_id = cq.concurrent_queue_id 
AND cwr.queue_application_id = cq.application_id 
AND cq.LANGUAGE = 'US' 
AND cwr.requested_by = fu.user_id 
ORDER BY 5
Note: The same information can be seen in Administer Concurrent Manager form for each manager.

 17 .Checking the incompatibilities between the programs

 The below query can be used to find all incompatibilities in an application instance. 
SELECT a2.application_name, a1.user_concurrent_program_name, 
DECODE (running_type, 
'P', 'Program', 
'S', 'Request set', 
'UNKNOWN' 
) "Type", 
b2.application_name "Incompatible App", 
b1.user_concurrent_program_name "Incompatible_Prog", 
DECODE (to_run_type, 
'P', 'Program', 
'S', 'Request set', 
'UNKNOWN' 
) incompatible_type 
FROM apps.fnd_concurrent_program_serial cps, 
apps.fnd_concurrent_programs_tl a1, 
apps.fnd_concurrent_programs_tl b1, 
apps.fnd_application_tl a2, 
apps.fnd_application_tl b2 
WHERE a1.application_id = cps.running_application_id 
AND a1.concurrent_program_id = cps.running_concurrent_program_id 
AND a2.application_id = cps.running_application_id 
AND b1.application_id = cps.to_run_application_id 
AND b1.concurrent_program_id = cps.to_run_concurrent_program_id 
AND b2.application_id = cps.to_run_application_id 
AND a1.language = 'US' 
AND a2.language = 'US' 
AND b1.language = 'US' 
AND b2.language = 'US'
The table apps.fnd_concurrent_program_serial has the information about incompatibilities.
18 .How to find if module is installed or not in Oracle Apps

 We have 3 ways to find out if a module is installed in oracle apps

 1 We can run the following script
cd $AD_TOP/sql/adutconf.sql
In this script, there is a section->"Product Installation Status, Version Info and Patch Level"
In this, status of installed means the product is installed.

 2  Through OAM
In oracle apps, navigate to
OAM>Site Map>/License Manager>Reports>Licensed Products
Here filter the products by  using "Licensed". These are the licensed and installed products in oracle apps.

 3 Using a sql query->

 We can use the following query

 SELECT a.application_name,a.product_code,
DECODE (b.status, 'I', 'Installed', 'S', 'Shared', 'N/A') status, 
patch_level 
FROM apps.fnd_application_vl a, 
apps.fnd_product_installations b 
WHERE a.application_id = b.application_id
and b.status='I'
order by product_code asc;
19.How to Find out Product Installations and Patch Set level in Oracle Applications 11i ?

 Use Following Query to find out Product Installation
SELECT application_name "Application Name",
SUBSTR (application_short_name, 1, 10) "Short Name",
RPAD (DECODE (fpi.status, 'I', 'Installed', 'S', 'Shared Install', 'N', 'Not Installed' ), 14, ' ' ) "Install Status", SUBSTR (patch_level, 1, 12) "Patch Level", fa.BASEPATH "Basepath"
FROM fnd_product_installations fpi,
fnd_application fa,
fnd_application_tl fat
WHERE fa.application_id = fpi.application_id
AND fa.application_id = fat.application_id
ORDER BY fpi.application_id;
SELECT application_name,SUBSTR (application_short_name, 1, 10) "Short Name",SUBSTR (patch_level, 1, 12) "Patch Level", fa.BASEPATH "Basepath" FROM fnd_product_installations fpi,fnd_application fa,fnd_application_tl fat
 WHERE fa.application_id = fpi.application_id  AND fa.application_id = fat.application_id order by fa.BASEPATH;

  Select product_version,patch_level from fnd_product_installations where patch_level like '%AP%';

  select BASEPATH from fnd_application order by BASEPATH;

   
20. How to Find Environment Variables from database table - Oracle E-Business Suite
Are you running Oracle E-Business Suite (EBS) / Applications and want to get an operating system level environment variable value from a database table, for example for use in PL/SQL? Or perhaps to default a concurrent program parameter? Didn't think environment variables were stored in the database?
Try out out this query that shows you $FND_TOP:
select value
from   fnd_env_context
where  variable_name = 'FND_TOP'
and    concurrent_process_id = 
      ( select max(concurrent_process_id) from fnd_env_context );

 VALUE
--------------------------------------------------------------------------------
/d01/oracle/VIS/apps/apps_st/appl/fnd/12.0.0
------------------------------------------------------------------------------- 

Or

 Did you want to find out the Product "TOP" directories e.g the full directory path values from fnd_appl_tops under APPL_TOP?
col variable_name format a15
col value format a64
select variable_name, value
from   fnd_env_context
where  variable_name like '%\_TOP' escape '\'
and    concurrent_process_id = 
     ( select max(concurrent_process_id) from fnd_env_context )
order by 1;

 VARIABLE_NAME   VALUE
--------------- ----------------------------------------------------------------
AD_TOP          /d01/oracle/VIS/apps/apps_st/appl/ad/12.0.0
AF_JRE_TOP      /d01/oracle/VIS/apps/tech_st/10.1.3/appsutil/jdk/jre
AHL_TOP         /d01/oracle/VIS/apps/apps_st/appl/ahl/12.0.0
AK_TOP          /d01/oracle/VIS/apps/apps_st/appl/ak/12.0.0
ALR_TOP         /d01/oracle/VIS/apps/apps_st/appl/alr/12.0.0
AME_TOP         /d01/oracle/VIS/apps/apps_st/appl/ame/12.0.0
AMS_TOP         /d01/oracle/VIS/apps/apps_st/appl/ams/12.0.0
AMV_TOP         /d01/oracle/VIS/apps/apps_st/appl/amv/12.0.0
AMW_TOP         /d01/oracle/VIS/apps/apps_st/appl/amw/12.0.0
APPL_TOP        /d01/oracle/VIS/apps/apps_st/appl
AP_TOP          /d01/oracle/VIS/apps/apps_st/appl/ap/12.0.0
AR_TOP          /d01/oracle/VIS/apps/apps_st/appl/ar/12.0.0
...


21.How to find  the full directory path to $APPLTMP?

select value
from   fnd_env_context
where  variable_name = 'APPLTMP'
and    concurrent_process_id = 
      ( select max(concurrent_process_id) from fnd_env_context );

 VALUE
--------------------------------------------------------------------------------
/d01/oracle/VIS/inst/apps/VIS_demo/appltmp
NB: These queries assume your concurrent managers are running!



22. Script to Get OS user name with terminal name
REM: Script to Get Os user name with terminal name
REM:*****************************************
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
REM: Author will not be responsible for any damage that may be cause by this script.
REM:*****************************************

 SELECT
DBA_USERS.USERNAME USERNAME,
DECODE(V$SESSION.USERNAME, NULL, 'NOT CONNECTED', 'CONNECTED') STATUS,
NVL(OSUSER, '-') OSUSER,
NVL(TERMINAL,'-') TERMINAL,
SUM(DECODE(V$SESSION.USERNAME, NULL, 0,1)) SESSIONS
FROM
DBA_USERS, V$SESSION
WHERE DBA_USERS.USERNAME = V$SESSION.USERNAME (+)
GROUP BY
DBA_USERS.USERNAME,
DECODE(V$SESSION.USERNAME, NULL, 'NOT CONNECTED', 'CONNECTED'),
OSUSER,
TERMINAL
ORDER BY 1 ; 
23 .Query to Identify the Concurrent Program for Long time.....................
Step1 :  Run the first query ,  this will list all the programs that currently running in Application. Take the SID and use it in the second query.

 SELECT 
      f.user_name
      ,a.request_id "Req Id" 
      ,a.concurrent_program_id "Prg Id"
      ,a.RESPONSIBILITY_ID Responsibility
      ,a.phase_code,a.status_code
      ,b.os_process_id "OS"
      ,vs.sid
      ,vs.serial# "Serial#"
      ,vp.spid
      ,TO_CHAR(request_date,'DD-MON-YY hh24:mi:ss') request_date
      ,(NVL(a.actual_completion_date,SYSDATE)-a.actual_start_date)*1440 "Time"
      ,c.concurrent_program_name||' - '||c2.user_concurrent_program_name "Program"
FROM APPLSYS.fnd_Concurrent_requests a
    ,APPLSYS.fnd_concurrent_processes b
    ,applsys.fnd_concurrent_queues q
    ,APPLSYS.fnd_concurrent_programs_tl c2
    ,APPLSYS.fnd_concurrent_programs c
    ,APPLSYS.fnd_user f
    ,v$session vs
    ,v$process vp
WHERE 
      a.controlling_manager = b.concurrent_process_id
  AND a.concurrent_program_id = c.concurrent_program_id
  AND a.program_application_id = c.application_id
  AND c2.concurrent_program_id = c.concurrent_program_id
  AND c2.application_id = c.application_id
  AND a.phase_code IN ('I','P','R','T')
  AND a.requested_by = f.user_id
  AND b.queue_application_id = q.application_id
  AND b.concurrent_queue_id = q.concurrent_queue_id
  AND c2.LANGUAGE = 'US' 
  AND a.oracle_process_id = vp.spid
  AND vs.paddr = vp.addr 
ORDER BY 9

 Step 2 : Get Sid from step1 and Keep on executing this query in SQL. This query will show the currently running SQL in the DB, as your concurrent is submitted and running. You can now find out the exact query  ( select / insert / update ) which is actually taking time in your concurrent program.
SELECT sql_text FROM v$sqltext t,v$session s
WHERE t.ADDRESS = s.SQL_ADDRESS
AND t.HASH_VALUE = s.SQL_HASH_VALUE
AND s.sid = 100 – Get this value from step1
ORDER BY PIECE 
 Step 3 :
------------
The following query finds total run-time (in minutes) for a concurrent program. Thus, with a little modification to this query, you can track which concurrent programs take (very) long time to complete, and may need performance tuning.

 Change the concurrent program name (tl.user_concurrent_program_name, see below) according to your search criteria. In this example, my concurrent program is "Autoinvoice Import Program". You can also uncomment the "&Start_Date" line to get the list for a specific date.

-------------------------------------------------------------------------------
-- Query to find runtime for a concurrent program
-------------------------------------------------------------------------------
SELECT /*+ rule */
       rq.parent_request_id                   "Parent Req. ID",
       rq.request_id                          "Req. ID",
       tl.user_concurrent_program_name        "Program Name",
       rq.actual_start_date                   "Start Date",
       rq.actual_completion_date              "Completion Date",
       ROUND((rq.actual_completion_date -
           rq.actual_start_date) * 1440, 2)   "Runtime (in Minutes)"      
  FROM applsys.fnd_concurrent_programs_tl  tl,
       applsys.fnd_concurrent_requests     rq
 WHERE tl.application_id        = rq.program_application_id
   AND tl.concurrent_program_id = rq.concurrent_program_id
   AND tl.LANGUAGE              = USERENV('LANG')
   AND rq.actual_start_date IS NOT NULL
   AND rq.actual_completion_date IS NOT NULL
   AND tl.user_concurrent_program_name = 'Autoinvoice Import Program'  -- <change it>
   -- AND TRUNC(rq.actual_start_date) = '&start_date'  -- uncomment this for a specific date
 ORDER BY rq.request_id DESC;


************************************************************* 
24 .Script to reset Oracle Apps front end User ID Password

Here is a  sample anonymous PL/SQL block which will reset the Oracle Apps frontend password for a given user from backend

DECLARE
      flag_value BOOLEAN;
BEGIN
      flag_value :=
           fnd_user_pkg.changepassword(username=> 'NKUMAR2'
                                                 ,newpassword => 'welcome1');
     IF flag_value
     THEN
           DBMS_OUTPU.PUT_LINE('The password reset successfully');
     ELSE
           DBMS_OUTPUT.PUT_LINE('The password reset has failed');
     END IF;

 END;
/
COMMIT;
25 .Checking the duplicated schedules of the same program with the same arguments

 The below query can be used to check the duplicated schedule of the same program with the same arguments. This can be used to alert the users to cancel these duplicated schedules. 

 Note: This query will return even though the request was submitted using a different responsibility. 

 SELECT request_id, NAME, argument_text, user_name 
FROM (SELECT cr.request_id, 
DECODE (cp.user_concurrent_program_name, 
'Report Set', 'Report Set:' || cr.description, 
cp.user_concurrent_program_name 
) NAME, 
argument_text, fu.user_name 
FROM apps.fnd_concurrent_programs_tl cp, 
apps.fnd_concurrent_requests cr, 
apps.fnd_user fu 
WHERE cp.application_id = cr.program_application_id 
AND cp.concurrent_program_id = cr.concurrent_program_id 
AND cr.requested_by = fu.user_id 
AND cr.phase_code = 'P' 
AND cr.requested_start_date > SYSDATE 
AND cp.LANGUAGE = 'US' 
AND fu.user_name NOT LIKE 'PPG%') t1 
WHERE EXISTS ( 
SELECT 1 
FROM (SELECT cr.request_id, 
DECODE (cp.user_concurrent_program_name, 
'Report Set', 'Report Set:' 
|| cr.description, 
cp.user_concurrent_program_name 
) NAME, 
argument_text, fu.user_name 
FROM apps.fnd_concurrent_programs_tl cp, 
apps.fnd_concurrent_requests cr, 
apps.fnd_user fu 
WHERE cp.application_id = cr.program_application_id 
AND cp.concurrent_program_id = 
cr.concurrent_program_id 
AND cr.requested_by = fu.user_id 
AND cr.phase_code = 'P' 
AND cr.requested_start_date > SYSDATE 
AND cp.LANGUAGE = 'US' 
AND fu.user_name NOT LIKE 'PPG%') t2 
WHERE t1.NAME = t2.NAME 
AND t1.argument_text = t2.argument_text 
AND t1.user_name = t2.user_name 
GROUP BY NAME, argument_text, user_name 
HAVING COUNT (*) > 1) 
ORDER BY user_name, NAME;
26.Average pending time per request
This is a very useful query to check the performance of the concurrent managers.
Average pending time for a request is calculated like below: 
("Highest of Requested_start_date or Date_submitted" - Actual_start_date ) / Total requests 

 A Request can be in Pending state for variety of reasons like conflict with other requests, improperly tuned managers (sleep seconds / cache size / number of managers etc) 

 We can schedule this script to gather data regularly for historical analysis as we normally purge the concurrent requests regularly. 
SELECT TO_CHAR (actual_start_date, 'DD-MON-YYYY') DAY, 
concurrent_queue_name, 
(SUM ( ( actual_start_date 
- (CASE 
WHEN requested_start_date > request_date 
THEN requested_start_date 
ELSE request_date 
END 
) 
) 
* 24 
* 60 
* 60 
) 
) 
/ COUNT (*) "Wait_Time_per_Req_in_Secs" 
FROM apps.fnd_concurrent_requests cr, 
apps.fnd_concurrent_processes fcp, 
apps.fnd_concurrent_queues fcq 
WHERE cr.phase_code = 'C' 
AND cr.actual_start_date IS NOT NULL 
AND cr.requested_start_date IS NOT NULL 
AND cr.controlling_manager = fcp.concurrent_process_id 
AND fcp.queue_application_id = fcq.application_id 
AND fcp.concurrent_queue_id = fcq.concurrent_queue_id 
GROUP BY TO_CHAR (actual_start_date, 'DD-MON-YYYY'), concurrent_queue_name 
ORDER BY 2;
Note: Depending on the purging schedules some requests might miss if the corresponding data in fnd_concurrent_processes is purged.
27.Script to Monitor Concurrent requests average run time

 Do you need a daily  report which would give an average run time of all concurrent requests in your environment?

 So you would know which concurrent request is taking longer time and on which day:
The below script gives an average run time for all concurrent requests for the current day, previous day, day before yesterday and for the whole week:

set pagesize 500
set echo off
set feedback off
set linesize 200
col USER_CONCURRENT_PROGRAM_NAME for a75
col wkly_Time Heading 'Weekly Run | Time | Avg |(In Minutes) '
col dbydy_Tim Heading 'Day Before|Yesterday | Run Time | Avg |(In Minutes) '
col ydy_Tim Heading 'Yesterday | Run Time | Avg |(In Minutes) '
col tdy_Tim Heading 'Today | Run Time | Avg |(In Minutes) '
col tdy_Tim Heading &_DATE
select  wkly.user_concurrent_program_name, wkly.wkly_time , to_char(nvl(dbydy.dbydy_time,'     Not Run'))  Dbydy_tim,to_char(nvl(ydy.ydy_time,'     Not Run'))  ydy_tim,to_char(nvl(tdy.tdy_time,'     Not Run'))  tdy_tim
FROM
(SELECT user_concurrent_program_name,
to_char(avg(round((actual_completion_date- actual_start_date)*24*60)),'99999999.99') wkly_time
FROM apps.fnd_conc_req_summary_v
WHERE nvl(actual_start_date,sysdate) >= (sysdate-7)
AND phase_code='C' and status_code  in ('C','G')
group by user_concurrent_program_name) wkly,
(SELECT user_concurrent_program_name,
to_char(avg(round((actual_completion_date- actual_start_date)*24*60)),'99999999.99') dbydy_time
FROM apps.fnd_conc_req_summary_v
WHERE trunc(nvl(actual_start_date,sysdate)) = trunc((sysdate-2))
AND phase_code='C' and status_code  in ('C','G')
group by user_concurrent_program_name ) dbydy,
(SELECT user_concurrent_program_name,
to_char(avg(round((actual_completion_date- actual_start_date)*24*60)),'99999999.99') ydy_time
FROM apps.fnd_conc_req_summary_v
WHERE trunc(nvl(actual_start_date,sysdate)) = trunc((sysdate-1))
AND phase_code='C' and status_code  in ('C','G')
group by user_concurrent_program_name ) ydy,
(SELECT user_concurrent_program_name,
to_char(avg(round((actual_completion_date- actual_start_date)*24*60)),'99999999.99') tdy_time
FROM apps.fnd_conc_req_summary_v
WHERE trunc(nvl(actual_start_date,sysdate)) = trunc((sysdate))
AND phase_code='C' and status_code  in ('C','G')
group by user_concurrent_program_name ) tdy
WHERE wkly.user_concurrent_program_name =dbydy.user_concurrent_program_name (+)
AND   dbydy.user_concurrent_program_name = ydy.user_concurrent_program_name (+)
AND   ydy.user_concurrent_program_name  = tdy.user_concurrent_program_name (+)
order by wkly_time desc;

28. Find currently spooling temp file from request
col outfile format a30
col logfile format a30
select cp.plsql_dir || '/' || cp.plsql_out outfile
,      cp.plsql_dir || '/' || cp.plsql_log logfile
from  apps.fnd_concurrent_requests cr
,     apps.fnd_concurrent_processes cp
where cp.concurrent_process_id = cr.controlling_manager
and cr.request_id = &request_id;

 OUTFILE                        LOGFILE
------------------------------ ------------------------------
/usr/tmp/PROD/o0068190.tmp     /usr/tmp/PROD/l0068190.tmp

 REM Now tail log file on database node to see where it is at, near realtime
REM tail -f /usr/tmp/l0068190.tmp



29.Currently held locks per concurrent request
set lines 150
col object_name format a32
col mode_held format a15
select /*+ ordered */
       fcr.request_id
,      object_name
,      object_type
,      decode( l.block
             , 0, 'Not Blocking'
             , 1, 'Blocking'
             , 2, 'Global'
             ) status
,      decode( v.locked_mode
             , 0, 'None'
             , 1, 'Null'
             , 2, 'Row-S (SS)'
             , 3, 'Row-X (SX)'
             , 4, 'Share'
             , 5, 'S/Row-X (SSX)'
             , 6, 'Exclusive'
             , to_char(lmode)
             ) mode_held
from   apps.fnd_concurrent_requests fcr
,      gv$process pro
,      gv$session sess
,      gv$locked_object v
,      gv$lock l
,      dba_objects d
where  fcr.phase_code = 'R'
and    fcr.oracle_process_id = pro.spid (+)
and    pro.addr = sess.paddr (+)
and    sess.sid = v.session_id (+)
and    v.object_id = d.object_id (+)
and    v.object_id = l.id1 (+)
;

 REQUEST_ID OBJECT_NAME                      OBJECT_TYPE         STATUS       MODE_HELD
---------- -------------------------------- ------------------- ------------ ---------------
   1070780 VIRTUATE_GL_OLAP_REFRESH         TABLE               Not Blocking Exclusive



-------------------------------------------------------------------------------

 -- Query to find concurrent request status related information

 -------------------------------------------------------------------------------

 SELECT fu.user_name                           "User ID",

        frt.responsibility_name                "Responsibility Used",

        fcr.request_id                         "Request ID",

        fcpt.user_concurrent_program_name      "Concurrent Program Name",

        DECODE(fcr.phase_code,

               'C',  'Completed',

               'P',  'Pending',

               'R',  'Running',

               'I',  'Inactive',

               fcr.phase_code)                 "Phase",

        DECODE(fcr.status_code,

               'A',  'Waiting',

               'B',  'Resuming',

               'C',  'Normal',

               'D',  'Cancelled',

               'E',  'Error',

               'F',  'Scheduled',

               'G',  'Warning',

               'H',  'On Hold',

               'I',  'Normal',

               'M',  'No Manager',

               'Q',  'Standby',

               'R',  'Normal',

               'S',  'Suspended',

               'T',  'Terminating',

               'U',  'Disabled',

               'W',  'Paused',

               'X',  'Terminated',

               'Z',  'Waiting',

               fcr.status_code)                "Status",

        fcr.request_date                       "Request Date",

        fcr.requested_start_date               "Request Start Date",

        fcr.hold_flag                          "Hold Flag",

        fcr.printer                            "Printer Name",

        fcr.parent_request_id                  "Parent Request ID"

        -- fcr.number_of_arguments,

        -- fcr.argument_text,

        -- fcr.logfile_name,

        -- fcr.outfile_name

   FROM fnd_user                    fu,

        fnd_responsibility_tl       frt,

        fnd_concurrent_requests     fcr,

        fnd_concurrent_programs_tl  fcpt

  WHERE fu.user_id                 =  fcr.requested_by

    AND fcr.concurrent_program_id  =  fcpt.concurrent_program_id

    AND fcr.responsibility_id      =  frt.responsibility_id

    AND frt.LANGUAGE               =  USERENV('LANG')

    AND fcpt.LANGUAGE              =  USERENV('LANG')

    -- AND fcr.request_id = 7137350  -- <change it>

    AND fcpt.user_concurrent_program_name = 'Autoinvoice Import Program'  -- <change it>

  ORDER BY fcr.request_date DESC;



30) CONCURRENT REQUEST MONITORING QUERIES

 SQL TO FIND OUT CONCURRENT REQUESTS CURRENTLY RUNNING:
**********************************************************
set lines 180
set pages 1000
set verify off
undef spid
column req_id format 99999999999
column OPID format a10
column PPID format a8
column SPID format a8
column ST_CD format a1
column ph_cd format a1
column CNAME format a30
column event format a15
column user_name format a10
column program format a8
column serial# format 999999
column sid format 9999
column username format a8
select a.request_id "REQ_ID",a.oracle_process_id "OPID",a.os_process_id
"PPID",
e.user_concurrent_program_name "CNAME",
f.user_name,a.status_code "ST_CD",a.phase_code "PH_CD", b.username,b.sid,
b.serial#,b.program,g.event,
to_char(a.ACTUAL_START_DATE,'MON-DD-HH-MI-SS') START_DATE,
to_char(a.ACTUAL_COMPLETION_DATE,'MON-DD-HH-MI-SS') COMPL_DATE
from apps.fnd_concurrent_requests a,(select c.username,c.sid,c.serial#,
                        c.program,d.spid from v$session c, v$process d
                        where c.paddr=d.addr) b,
                        apps.fnd_concurrent_programs_tl e,
                        apps.fnd_user f,
                        v$session_wait g
                        where a.oracle_process_id=b.spid
                        and a.concurrent_program_id=e.concurrent_program_id
                        and e.language='US'
                        and a.requested_by=f.user_id
                        and b.sid=g.sid
            and a.status_code='R'
            and a.phase_code='R';



********************************************************

 Retrieve a list of all responsibilities.
Parameters
None
*//

 SELECT
(SELECT application_short_name
FROM fnd_application fa
WHERE fa.application_id = frt.application_id)
application
, frt.responsibility_id
, frt.responsibility_name
FROM apps.fnd_responsibility_tl frt;

//*
2. Menus Listing
Purpose/Description:
To see the Menus associated with a given responsibility
Parameters
responsibility_id that you can retrieve from query nr 1 (Responsibilities Listing)
*//

 SELECT DISTINCT
a.responsibility_name
, c.user_menu_name
FROM
apps.fnd_responsibility_tl a
, apps.fnd_responsibility b
, apps.fnd_menus_tl c
, apps.fnd_menus d
, apps.fnd_application_tl e
, apps.fnd_application f
WHERE
a.responsibility_id(+) = b.responsibility_id
AND a.responsibility_id = 50103
AND b.menu_id = c.menu_id
AND b.menu_id = d.menu_id
AND e.application_id = f.application_id
AND f.application_id = b.application_id
AND a.LANGUAGE = ‘US’;

//*
3. Submenu And Function Listing
Purpose/Description:
By using this query you can check function and submenus attached to a specific menu
Parameters
User_menu_name that you can get by running query 2 (Menu Listing)
*//

 SELECT
c.prompt
, c.description
FROM
apps.fnd_menus_tl a
, fnd_menu_entries_tl c
WHERE
a.menu_id = c.menu_id
AND a.user_menu_name = ‘Navigator Menu – System Administrator GUI’;

//*
4.User and Assigned Responsibility Listing
Purpose/Description:
You can use this query to check responsibilities assigned to users.
Parameters
None
*//

 SELECT UNIQUE
u.user_id
, SUBSTR (u.user_name, 1, 30) user_name
, SUBSTR (r.responsibility_name, 1, 60) responsiblity
, SUBSTR (a.application_name, 1, 50) application
FROM
fnd_user u
, fnd_user_resp_groups g
, fnd_application_tl a
, fnd_responsibility_tl r
WHERE
g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
ORDER BY
SUBSTR (user_name, 1, 30)
, SUBSTR (a.application_name, 1, 50)
, SUBSTR (r.responsibility_name, 1, 60);

//*
5. Responsibility and assigned request group listing
Purpose/Description:
To find responsibility and assigned request groups.
Every responsibility contains a request group (The request group is basis of submitting requests)
Parameters
None
*//

 SELECT
responsibility_name responsibility
, request_group_name
, frg.description
FROM
fnd_request_groups frg
, fnd_responsibility_vl frv
WHERE
frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name

//*
6. Profile option with modification date and user
Purpose/Description:
Query that can be used to audit profile options.
Parameters
None
*//

 SELECT
t.user_profile_option_name
, profile_option_value
, v.creation_date
, v.last_update_date
, v.creation_date – v.last_update_date "Change Date"
, (SELECT UNIQUE user_name
FROM fnd_user
WHERE user_id = v.created_by) "Created By"
, (SELECT user_name
FROM fnd_user
WHERE user_id = v.last_updated_by) "Last Update By"
FROM
fnd_profile_options o
, fnd_profile_option_values v
, fnd_profile_options_tl t
WHERE
o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND start_date_active <= SYSDATE
AND NVL (end_date_active, SYSDATE) >= SYSDATE
AND o.profile_option_name = t.profile_option_name
AND level_id = 10001
AND t.LANGUAGE IN (SELECT language_code
FROM fnd_languages
WHERE installed_flag = ‘B’
UNION
SELECT nls_language
FROM fnd_languages
WHERE installed_flag = ‘B’)
ORDER BY user_profile_option_name;

//*
7. Forms personalization Listing
Purpose/Description:
To get modified profile options.
Personalization is a feature available in 11.5.10.X.
Parameters
None
*//

 SELECT
ffft.user_function_name "User Form Name"
, ffcr.SEQUENCE
, ffcr.description
, ffcr.rule_type
, ffcr.enabled
, ffcr.trigger_event
, ffcr.trigger_object
, ffcr.condition
, ffcr.fire_in_enter_query
, (SELECT user_name
FROM fnd_user fu
WHERE fu.user_id = ffcr.created_by) "Created By”
FROM
fnd_form_custom_rules ffcr
, fnd_form_functions_vl ffft
WHERE ffcr.ID = ffft.function_id
ORDER BY 1;

//*
8. Patch Level Listing
Purpose/Description:
Query that can be used to view the patch level status of all modules
Parameters
None
*// 

 SELECT
a.application_name
, DECODE (b.status, ‘I’, ‘Installed’, ‘S’, ‘Shared’, ‘N/A’) status
, patch_level
FROM
apps.fnd_application_vl a
, apps.fnd_product_installations b
WHERE
a.application_id = b.application_id;


//*
9. Request attached to responsibility listing
Purpose/Description:
To see all requests attached to a responsibility
Parameters
None
*//

 SELECT
responsibility_name
, frg.request_group_name
, fcpv.user_concurrent_program_name
, fcpv.description
FROM
fnd_request_groups frg
, fnd_request_group_units frgu
, fnd_concurrent_programs_vl fcpv
, fnd_responsibility_vl frv
WHERE
frgu.request_unit_type = ‘P’
AND frgu.request_group_id = frg.request_group_id
AND frgu.request_unit_id = fcpv.concurrent_program_id
AND frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name;

//*
10. Request listing application wise
Purpose/Description:
View all request types application wise
Parameters
None
*// 

 SELECT
fa.application_short_name
, fcpv.user_concurrent_program_name
, description
, DECODE (fcpv.execution_method_code
,’B', ‘Request Set Stage Function’
,’Q', ‘SQL*Plus’
,’H', ‘Host’
,’L', ‘SQL*Loader’
,’A', ‘Spawned’
,’I', ‘PL/SQL Stored Procedure’
,’P', ‘Oracle Reports’
,’S', ‘Immediate’
,fcpv.execution_method_code) exe_method
, output_file_type
, program_type
, printer_name
, minimum_width
, minimum_length
, concurrent_program_name
, concurrent_program_id
FROM
fnd_concurrent_programs_vl fcpv
, fnd_application fa
WHERE
fcpv.application_id = fa.application_id
ORDER BY description

//*
11. Count Reports per module
Purpose/Description:
To Count Reports
Parameters
None
*//

 SELECT
fa.application_short_name
, DECODE (fcpv.execution_method_code
,’B', ‘Request Set Stage Function’
,’Q', ‘SQL*Plus’
,’H', ‘Host’
,’L', ‘SQL*Loader’
,’A', ‘Spawned’
,’I', ‘PL/SQL Stored Procedure’
,’P', ‘Oracle Reports’
,’S', ‘Immediate’
,fcpv.execution_method_code) exe_method
, COUNT (concurrent_program_id) COUNT
FROM
fnd_concurrent_programs_vl fcpv
, fnd_application fa
WHERE
fcpv.application_id = fa.application_id
GROUP BY
fa.application_short_name
, fcpv.execution_method_code
ORDER BY 1;

//*
12. Request Status Listing
Purpose/Description:
This query returns report/request processing time
Parameters
None
*//

 SELECT
f.request_id
, pt.user_concurrent_program_name user_concurrent_program_name
, f.actual_start_date actual_start_date
, f.actual_completion_date actual_completion_date
, floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)
‘ HOURS ‘
floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)
‘ MINUTES ‘
round((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60) ))
‘ SECS ‘ time_difference
, DECODE(p.concurrent_program_name
,’ALECDC’
,p.concurrent_program_name’['
f.description']‘
,p.concurrent_program_name) concurrent_program_name
, decode(f.phase_code
,’R',’Running’
,’C',’Complete’
,f.phase_code) Phase
, f.status_code
FROM
apps.fnd_concurrent_programs p
, apps.fnd_concurrent_programs_tl pt
, apps.fnd_concurrent_requests f
WHERE
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’)
AND f.actual_start_date is not null
ORDER by f.actual_completion_date-f.actual_start_date desc;

//*
13. User and responsibility listing
Purpose/Description:
Check responsibilities assigned to users
Parameters
None
*// 

 SELECT UNIQUE
u.user_id
, SUBSTR (u.user_name, 1, 30) user_name
, SUBSTR (r.responsibility_name, 1, 60) responsiblity
, SUBSTR (a.application_name, 1, 50) application
FROM
fnd_user u
, fnd_user_resp_groups g
, fnd_application_tl a
, fnd_responsibility_tl r
WHERE g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
–AND a.application_name like ‘%Order Man%’
ORDER BY SUBSTR (user_name, 1, 30),
SUBSTR (a.application_name, 1, 50),
SUBSTR (r.responsibility_name, 1, 60)

//*
14. Applied Patch Listing
Purpose/Description:
Check Current Applied Patches
Parameters
None
*//

 SELECT
patch_name
, patch_type
, maint_pack_level
, creation_date
FROM applsys.ad_applied_patches
ORDER BY creation_date DESC 


15. How to check the System Administarator responsiblity for the users in ORACLE EBS ?

 set lines 2000
set pages 5000
col USER_NAME for a45

 Select b.user_name, c.responsibility_name, a.START_DATE, a.END_DATE
from fnd_user_resp_groups_direct a, fnd_user b, fnd_responsibility_tl c
where a.user_id = b.user_id
and a.responsibility_id = c.responsibility_id

and c.responsibility_name='System Administrator';

 **********************************************************************
My connection Kiran Jadhav has also shared some of the daily pocket scripts which DBAs need in their daily task. You can refer to those. Below is the link to his blog:
https://h2hdba.blogspot.in/p/dba-shell-scripts.html



1 comment:

Subscribe to: Posts (Atom)