How to kill the expdp jobs from backend.

1. First check what datapump jobs are running:


SQL> select * from dba_datapump_jobs;
OWNER_NAME  JOB_NAME              OPERATION   JOB_MODE   STATE      DEGREE ATTACHED_SESSIONS
------------------------------ ------------------------------ ------------------------------
SYSTEM      SYS_EXPORT_FULL_01    EXPORT        FULL   EXECUTING     32         1



2. To stop/kill the datapump job, login as sysdba and execute from sqlplus:


DECLARE
   h1 NUMBER;
BEGIN
   h1 := DBMS_DATAPUMP.ATTACH('JOB_NAME','OWNER_NAME');
   DBMS_DATAPUMP.STOP_JOB (h1,1,0);
END;
/

for example:

DECLARE
   h1 NUMBER;
BEGIN
   h1 := DBMS_DATAPUMP.ATTACH('SYS_EXPORT_FULL_01','SYSTEM');
   DBMS_DATAPUMP.STOP_JOB (h1,1,0);
END;
/


3. After stopping the datapump job, oracle removes and the dmp files that already have been produced. Check again:


SQL> select * from dba_datapump_jobs;
Reviews

1) Resource busy and acquire with NOWAIT specified

Cause  ::   Your table is already locked by some query. Like you have executed "select for update" and has yet not committed/rollback and again fired select query. Do a commit/rollback before executing your query.

Find query  which caused the error ::

SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,

S.MACHINE,S.PORT , S.LOGON_TIME,SQ.SQL_FULLTEXT

FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S,

V$PROCESS P, V$SQL SQ

WHERE L.OBJECT_ID = O.OBJECT_ID

AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR

AND S.SQL_ADDRESS = SQ.ADDRESS;

2) Who is consuming more CPU

select sid, serial#, program,module, osuser,machine from v$session where paddr= (select addr from v$process where spid=4357)

here 4357 is OS level id using TOP

 3)To  Know object which is locked

select

object_name,

object_type,

session_id,

type,                    -- Type or system/user lock

lmode,                -- lock mode in which session holds lock

request,

block,

ctime                   -- Time since current mode was granted

from

v$locked_object, all_objects, v$lock

where

  v$locked_object.object_id = all_objects.object_id AND

  v$lock.id1 = all_objects.object_id AND

  v$lock.sid = v$locked_object.session_id

order by

  session_id, ctime desc, object_name

4) SQL that is currently "ACTIVE or running

select S.USERNAME, s.sid, s.serial#,s.osuser, t.sql_id, sql_text

from v$sqltext_with_newlines t,V$SESSION s

where t.address =s.sql_address

and t.hash_value = s.sql_hash_value

and s.status = 'ACTIVE'

and s.username not in('SYSTEM','SYS','SYSMAN')

order by s.sid,t.piece;

by using the above sid we can also know the percentage of query execution done by below sql

COLUMN percent FORMAT 999.99

SELECT sid, to_char(start_time,'hh24:mi:ss') stime,

message,( sofar/totalwork)* 100 percent

FROM v$session_longops

WHERE sid=24;

5) Find temporary tablespace free space

select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

6)Tablespace free and used space

select df.tablespace_name "Tablespace",

totalusedspace "Used MB",

(df.totalspace - tu.totalusedspace) "Free MB",

df.totalspace "Total MB",

round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))

"Pct. Free"

from

(select tablespace_name,

round(sum(bytes) / 1048576) TotalSpace

from dba_data_files

group by tablespace_name) df,

(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name

from dba_segments

group by tablespace_name) tu

where df.tablespace_name = tu.tablespace_name ;

7)look up the sql,username,machine,port information and get to the actual process which holds the connection

SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,

S.MACHINE,S.PORT , S.LOGON_TIME,SQ.SQL_FULLTEXT 

FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, 

V$PROCESS P, V$SQL SQ 

WHERE L.OBJECT_ID = O.OBJECT_ID 

AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR 

AND S.SQL_ADDRESS = SQ.ADDRESS;

8)how to view view definition

select TEXT  FROM DBA_VIEWS  where OWNER = '<owner_name>' and VIEW_NAME  = '<view_name>';

9)Check percentage of RMAN job Completed

select sid, start_time, totalwork sofar, (sofar/totalwork) * 100 pct_done from gv$session_longops where totalwork > sofar AND opname NOT LIKE '%aggregate%' AND opname like 'RMAN%';

SELECT

opname,

target,

ROUND( ( sofar/totalwork ), 4 ) * 100 Percentage_Complete,

start_time,

CEIL( time_remaining / 60 ) Max_Time_Remaining_In_Min,

FLOOR( elapsed_seconds / 60 ) Time_Spent_In_Min

FROM v$session_longops

WHERE sofar != totalwork;

10)Temp tablespace free space.

SELECT tablespace_name, SUM (bytes_used), SUM (bytes_free) FROM v$temp_space_header GROUP BY tablespace_name;

11) ORA-01940: cannot drop a user that is currently connected

SQL>drop user username cascade;
drop user username cascade

*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

SQL>select s.sid, s.serial#, s.status, p.spid from v$session s, v$process p where s.username = 'ISL_DF' and p.addr (+) = s.paddr;

SID           SERIAL# STATUS    SPID

----------   ----------    --------       ------------
159               29          INACTIVE 12349

SQL>alter system kill session '159,29';

System altered.
SQL>!kill -9 12349            - (kill the process in OS level)
SQL>drop user username cascade;
User dropped.

12) impdp: ORA-39083: Object type TYPE failed to create with error: ORA-02304: invalid object identifier literal

Error Message:
ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE TYPE "TEST"."TTTTT"   OID '8E21EF4C1F6EF167E04011AC99643EB0' IS TABLE OF VARCHAR2(40);

This error happened when expdp and impdp a schema to a new one in the same database. 

Cause: OID should be unique in a database. the OID in the impdp create statemene was used by old schema. 

solution: 

1. remove the OID from failing sql and rerun it manually.
2. impdp again with parameter transform=OID:n

13) R12 Clone Error - Unable to find PD KSH version

ERROR  :: Checking for make...    found - /usr/bin/make

Checking for ld...     found - /usr/bin/ld

Checking for gcc...    found - /usr/bin/gcc

Checking for g++...    found - /usr/bin/g++

Checking for ar...     found - /usr/bin/ar

Checking for ksh...

Unable to find PD KSH version.

Unable to locate all utilities with system path.

PATH = /u01/oracle/PROD/db/tech_st/11.2.3/appsutil/clone/bin/../jre/bin:/usr/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin

Solution :: export KSH_VERSION='@(#)PD KSH v5.2.14 99/07/13.2'

14)  Remove the RPM in linux if dependencies exit

Solution ::

yum remove $(rpm -qa | grep PACKAGENAME)

* Change PACKAGENAME with your Package name

* For disabling plugins just add --disableplugin=PLUGIN-NAME

* If you can't access the Internet, just add this options to the line above --disablerepo=*

15) Identifying the locks and finds the serial# for the lock.

Solution::

SELECT l.inst_id,SUBSTR(L.ORACLE_USERNAME,1,8) ORA_USER, SUBSTR(L.SESSION_ID,1,3) SID,

S.serial#,

SUBSTR(O.OWNER||'.'||O.OBJECT_NAME,1,40) OBJECT, P.SPID OS_PID,

DECODE(L.LOCKED_MODE, 0,'NONE',1,'NULL',2,'ROW SHARE',3,'ROW EXCLUSIVE',4,'SHARE',

5,'SHARE ROW EXCLUSIVE',

6,'EXCLUSIVE',

NULL) LOCK_MODE

FROM sys.GV_$LOCKED_OBJECT L, DBA_OBJECTS O, sys.GV_$SESSION S, sys.GV_$PROCESS P

WHERE L.OBJECT_ID = O.OBJECT_ID

and l.inst_id = s.inst_id

AND L.SESSION_ID = S.SID

and s.inst_id = p.inst_id

AND S.PADDR = P.ADDR(+)

order by l.inst_id  ;

16)How to check the required code level

Sql>select * from fnd_product_installations where patch_level like '%ATG%’;

17)How to check the database uptime

SELECT host_name, instance_name,

TO_CHAR(startup_time, 'DD-MM-YYYY HH24:MI:SS') startup_time,

FLOOR(sysdate-startup_time) days

FROM   sys.v_$instance;

18) Find the rpm version

rpm -qa --queryformat "%{NAME}-%{VERSION}.%{RELEASE} (%{ARCH})\n" | grep elfuti

17) find the complete information of apps.

$ADPERLPRG $FND_TOP/patch/115/bin/TXKScript.pl -script=$FND_TOP/patch/115/bin/txkInventory.pl -txktop=$APPLTMP -contextfile=$CONTEXT_FILE -appspass=Today2014 -outfile=$APPLTMP/Report_App_Inventory.html

18) This will show the activity in the last 15 minutes. And also the users connected.

For 15 min ---sysdate -1/96

For 1 hour  --- sysdate -1/24

For 1 day   -- sysdate -1

select i.limit_time,F.USER_NAME,i.limit_connects, to_char(last_connect, 'DD-MON-RR HH:MI:SS') "Last Connection time",i.user_id, i.disabled_flag from icx_sessions i, FND_USER F where  last_connect > sysdate - 1/96 and i.user_id=F.user_id ;

19.Count of Concurrent users connected to Oracle Apps

select count(distinct d.user_name) from apps.fnd_logins a, v$session b, v$process c, apps.fnd_user d  where b.paddr = c.addr and a.pid=c.pid and a.spid = b.process and d.user_id = a.user_id and (d.user_name = 'USER_NAME' OR 1=1);

20.Enable Yum to download 32 bir rpms aslo

multilib_policy=all

Check the dblink

SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10)

||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)

||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING

'''||L.HOST||''''

||chr(10)||';' TEXT

FROM SYS.LINK$ L, SYS.USER$ U

WHERE L.OWNER# = U.USER#;

Check the profile option value from  backend

SELECT fpo.profile_option_name SHORT_NAME,

         fpot.user_profile_option_name NAME,

         DECODE (fpov.level_id,

                 10001, 'Site',

                 10002, 'Application',

                 10003, 'Responsibility',

                 10004, 'User',

                 10005, 'Server',

                 'UnDef')

            LEVEL_SET,

         DECODE (TO_CHAR (fpov.level_id),

                 '10001', '',

                 '10002', fap.application_short_name,

                 '10003', frsp.responsibility_key,

                 '10005', fnod.node_name,

                 '10006', hou.name,

                 '10004', fu.user_name,

                 'UnDef')

            "CONTEXT",

         fpov.profile_option_value VALUE

    FROM fnd_profile_options fpo,

         fnd_profile_option_values fpov,

         fnd_profile_options_tl fpot,

         fnd_user fu,

         fnd_application fap,

         fnd_responsibility frsp,

         fnd_nodes fnod,

         hr_operating_units hou

   WHERE     fpo.profile_option_id = fpov.profile_option_id(+)

         AND fpo.profile_option_name = fpot.profile_option_name

         AND fu.user_id(+) = fpov.level_value

         AND frsp.application_id(+) = fpov.level_value_application_id

         AND frsp.responsibility_id(+) = fpov.level_value

         AND fap.application_id(+) = fpov.level_value

         AND fnod.node_id(+) = fpov.level_value

         AND hou.organization_id(+) = fpov.level_value

         AND fpot.user_profile_option_name IN ('&User_Profile_Option_Name')

ORDER BY short_name;

GATHER SCHEMA STATS from Back-end

Use the following command to gather schema statistics:
exec fnd_stats.gather_schema_statistics(‘ONT’) < For a specific schema >
exec fnd_stats.gather_schema_statistics(‘ALL’) < For all schemas >
Use the following command for gathering statistics on a temporary table
(ie: temporary tablename = TEMP_tmp in schema ABC):
exec fnd_stats.gather_table_stats('ABC’,'TEMP_tmp’);

This script will extract source code for package, package body,fucntion

set pages
set feedback off
set heading off
set verify off
set lines 300
set trims on

set term on
prompt +--------------------------------------------+
prompt DESCRIPTION:
prompt ============
prompt This script will extract source code for the following source types:
prompt 1. PACKAGE
prompt 2. PACKAGE BODY
prompt 3. FUNCTION
prompt 4. PROCEDURE
prompt The source code will be spooled into a file named after the source name
prompt USAGE:
prompt =====
prompt follow the prompts ...
prompt +---------------------------------------------+
accept proc_name  prompt "Enter Procedure Or Package Name: "
accept proc_owner prompt "Enter The Owner Name: "
accept type prompt "Enter type Of the source [PACKAGE, PACKAGE BODY, PROCEDURE, FUNCTION]: "
set term off



SELECT DECODE(ROWNUM,1,'CREATE OR REPLACE '||text,text)
FROM   dba_source
WHERE  name  = UPPER('OE_DEBUG_PUB')----"Enter Procedure Or Package Name: "
AND    owner = UPPER('APPS') ------- "Enter The Owner Name: "
AND    type  = UPPER('PACKAGE')   -----"Enter type Of the source [PACKAGE, PACKAGE BODY, PROCEDURE, FUNCTION]: "
ORDER BY line;

Check the number or EBS users currently logged in ?

select last_connect, usr.user_name, resp.responsibility_key, function_type, icx.*
  from apps.icx_sessions icx
  join apps.fnd_user usr on usr.user_id=icx.user_id
  left join apps.fnd_responsibility resp on resp.responsibility_id=icx.responsibility_id
  where last_connect>sysdate-nvl(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'),30)/60/24
    and disabled_flag != 'Y' and pseudo_flag = 'N';

How  to generate the  output from query in html format oracle?

set ver off
set term off
set markup html on spool on

spool output.html

select * from dept;
select * from product;

spool off
exit

Check the Bi Loads  status.

Connect to <PREFIX>_BIA_ODIREPO/*****  connect to bi repository schema



SELECT TRUNC(SSR.SESS_END) AS SESSION_RAN_DATE,
SSR.CONTEXT_CODE,
SS.SCEN_NAME AS SCENARIO_NAME ,
SS.SCEN_VERSION AS SCEN_VERSION ,
null AS SESS_NO,
SSR.SESS_DUR AS DUR,
SSR.SESS_BEG,
SSR.SESS_END,
DECODE(SSR.SESS_STATUS,'D','SUCCESSFUL','E','FAILED')AS STATUS,
SSR.NB_ROW AS ROW_PROCESSED,
SSR.NB_INS AS ROWS_INSERTED,
SSR.NB_UPD AS ROWS_UPDATED,
SSR.NB_DEL AS ROWS_DELETED,
SSR.NB_ERR AS ERROR_RECORD
FROM PRD_BIA_ODIREPO.SNP_SCEN SS, PRD_BIA_ODIREPO.SNP_SESSION  SSR
WHERE
SS.scen_name=SSR.scen_name
AND SESS_STATUS ='D'
and TRUNC(SSR.SESS_END) =to_Date('05/11/2018','MM/DD/YYYY')
ORDER BY SSR.SESS_END asc ; 

Oracle Apps DBA Useful concurrent request queries.


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

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

select fcq.application_id,
fcq.concurrent_queue_name,
fcq.user_concurrent_queue_name,
ftp.application_id,
ftp.concurrent_time_period_name,
fa.application_short_name,
ftp.description,
fcqs.min_processes,
fcqs.max_processes,
fcqs.sleep_seconds,
fcqs.service_parameters
from apps.fnd_concurrent_queues_vl fcq,
apps.fnd_concurrent_queue_size fcqs,
apps.fnd_concurrent_time_periods ftp,
apps.fnd_application fa
where fcq.application_id = fcqs.queue_application_id
and fcq.concurrent_queue_id = fcqs.concurrent_queue_id
and fcqs.period_application_id = ftp.application_id
and fcqs.concurrent_time_period_id = ftp.concurrent_time_period_id
and ftp.application_id = fa.application_id;

###############################################################
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.


#############################################################################
Query to find the Concurrent Requests which are incompatible with Request
##############################################################################3


--input:    Concurrent program name
SELECT fcpt.user_concurrent_program_name Input_Program,
            (SELECT fcpt1.user_concurrent_program_name Incompatible_Programs
                FROM fnd_concurrent_programs_tl   fcpt1
             WHERE fcpt1.concurrent_program_id in (fcps.to_run_concurrent_program_id)
                  AND rownum = 1) Incompatible_Programs
   FROM fnd_concurrent_program_serial fcps
            ,fnd_concurrent_programs_tl       fcpt     
WHERE fcps.running_concurrent_program_id = fcpt.concurrent_program_id
     AND fcpt.user_concurrent_program_name like '%'||:Program_Name||'%'

################################################################
Concurrent Program enabled 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;




#######################################

sid

############################


 SELECT B.SID, B.SERIAL#, B.INST_ID, B.EVENT, B.BLOCKING_SESSION, C.SQL_ID, C.PLAN_HASH_VALUE,C.module
FROM APPS.fnd_concurrent_requests A
,GV$SESSION B
,GV$SQLAREA C
WHERE A.oracle_session_id = B.audsid
-- AND a.phase_code = 'R'
AND B.sql_hash_value = C.hash_value
AND A.request_id in ('606963','606965','606964','606966','606967','606969','606970','606968','606971');


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

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

#######################################################################
find Concurrent Request using 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;


###########################################################
##list of running requests
###################################

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;


##############################################################

Concurrent program submitted with values and varibales
##############################################################
set lines 20000
col PROGRAM for a70
set pagesize 100
col ARGUMENT_TEXT for a50
select a.REQUEST_ID,b.program,a.ARGUMENT_TEXT,decode(a.status_code,'R','Normal','I','Normal','Z','Waiting','D','Cancelled','U','Disabled','E','Error','M','NoManager','C','Normal','H','On-Hold','W','Paused','B','Resuming','P','Scheduled','Q','Standby','S','Suspended','X','Terminated','T','Terminating','A','Waiting','G','Warning') Status,
decode(a.phase_code,'P','Pending','R','Running','C','Completed','I','Inactive') Phase,
to_char(b.ACTUAL_START_DATE,'dd/mm/yy hh24:mi:ss') "Start Time",
to_char(b.ACTUAL_COMPLETION_DATE,'dd/mm/yy hh24:mi:ss') "End Time" ,3600*(b.ACTUAL_COMPLETION_DATE-b.ACTUAL_START_DATE) "Execution Time"
from apps.fnd_concurrent_requests a,apps.FND_CONC_REQ_SUMMARY_V b where a.request_id=b.request_id and A.request_id in ('608397')    ;




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


#######################################################
Current running sql text for given concurrent request.
#######################################################

 SELECT A.REQUEST_ID, D.SID, D.SERIAL#, D.OSUSER, D.PROCESS, C.SPID,
       E.SQL_TEXT
  FROM APPS.FND_CONCURRENT_REQUESTS A,
       APPS.FND_CONCURRENT_PROCESSES B,
       V$PROCESS C,
       V$SESSION D,
       V$SQL E
WHERE A.CONTROLLING_MANAGER = B.CONCURRENT_PROCESS_ID
   AND C.PID = B.ORACLE_PROCESS_ID
   AND B.SESSION_ID = D.AUDSID
   AND D.SQL_ADDRESS = E.ADDRESS
   AND A.REQUEST_ID = 760781;
 
########################################################
To find child requests for parent id
########################################################

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

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

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

select s.sid, s.serial#,s.inst_id, s.program,p.spid from gv$session s , gv$process p where  p.addr =s.paddr and   s.sid in ('1192');


#######################################################
Sql text for the request id doing in the backend.
#######################################################

Based on the SPID associated to each running request, query the v$session orv$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;

-----------------------------------------------
SELECT A.REQUEST_ID, D.SID, D.SERIAL#, D.OSUSER, D.PROCESS, C.SPID,
       E.SQL_TEXT
  FROM APPS.FND_CONCURRENT_REQUESTS A,
       APPS.FND_CONCURRENT_PROCESSES B,
       V$PROCESS C,
       V$SESSION D,
       V$SQL E
WHERE A.CONTROLLING_MANAGER = B.CONCURRENT_PROCESS_ID
   AND C.PID = B.ORACLE_PROCESS_ID
   AND B.SESSION_ID = D.AUDSID
   AND D.SQL_ADDRESS = E.ADDRESS
   AND A.REQUEST_ID = 740473;

#########################################################################3
   To find child requests for parent id
#############################################################################3


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


##############################################################################
Check run time of concurrent program of specific date or for some days duration.
##############################################################################

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;

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

###########################################################
CPU might be more if request submitted by end date user
Requests submitted by end-dated users using the query below
############################################################

select fcr.request_id
, fcpt.user_concurrent_program_name
, fu.user_name submitted_by
, to_char(fcr.requested_start_date,'DD-MON-YYYY HH24:MM:SS') start_date
, 'Every '|| decode(length(fcrc.class_info),39,fcrc.class_info,
substr(fcrc.class_info,1,instr(fcrc.class_info,':',1)-1)||' '||
decode(substr(fcrc.class_info,instr(fcrc.class_info,':',1)+1,1)
, 'N','Minute(s) '
, 'D','Day(s) '
, 'H','Hour(s) '
, 'M','Month(s) ')|| 'after '||
decode(substr(fcrc.class_info,instr(fcrc.class_info,':',1,2)+1,1)
, 'S'
, 'Start '
, 'C'
, 'Completion ')|| 'of prior request' ) scheduled_as
, nvl(to_char(fcrc.end_date_active,'DD-MON-YYYY'),'forever') ending_on
, fu.end_date "USER'S END DATE"
from fnd_concurrent_requests fcr
, fnd_concurrent_programs_tl fcpt
, fnd_user fu
, fnd_conc_release_classes fcrc
where fcr.phase_code = 'P'
and fcr.status_code in ('I','Q')
and fcr.program_application_id = fcpt.application_id
and fcr.concurrent_program_id = fcpt.concurrent_program_id
and fcpt.language = 'US'
and fcr.requested_by = fu.user_id
and fcr.release_class_app_id = fcrc.application_id
and fcr.release_class_id = fcrc.release_class_id
and fu.end_date <= sysdate
order by fcr.requested_start_date desc;



####################################################################################################################################################3
Check the values for the "Concurrent:Sequential Requests", "Concurrent:Active Request Limit" and "Concurrent:Hold Requests (CONC_HOLD)" profile options.


Concurrent:Active Request Limit (CONC_REQUEST_LIMIT)

You can limit the number of requests that may be run simultaneously by each user or for every user at a site. If you do not specify a limit, no limit is imposed.
Concurrent:Sequential Requests (CONC_SINGLE_THREAD)

You can force your requests to run one at a time (sequentially) according to the requests' start dates and times, or allow them to run concurrently, when their programs are compatible.

A value of "Yes" prevents your requests from running concurrently. Requests run sequentially in the order they are submitted.
A value of "No" means your requests can run concurrently when their concurrent programs are compatible.

Concurrent:Hold Requests (CONC_HOLD)
You can automatically place your concurrent requests on hold when you submit them.

The default is "No". The concurrent managers run your requests according to the priority and start time specified for each.
"Yes" means your concurrent requests and reports are automatically placed on hold.
################################################################################################################################################


select fcr.request_id
, fcpt.user_concurrent_program_name
, fu.user_name submitted_by
, to_char(fcr.requested_start_date,'DD-MON-YYYY HH24:MM:SS') start_date
, 'Every '|| decode(length(fcrc.class_info),39,fcrc.class_info,
substr(fcrc.class_info,1,instr(fcrc.class_info,':',1)-1)||' '||
decode(substr(fcrc.class_info,instr(fcrc.class_info,':',1)+1,1)
, 'N','Minute(s) '
, 'D','Day(s) '
, 'H','Hour(s) '
, 'M','Month(s) ')|| 'after '||
decode(substr(fcrc.class_info,instr(fcrc.class_info,':',1,2)+1,1)
, 'S'
, 'Start '
, 'C'
, 'Completion ')|| 'of prior request' ) scheduled_as
, nvl(to_char(fcrc.end_date_active,'DD-MON-YYYY'),'forever') ending_on
, fu.end_date "USER'S END DATE"
from fnd_concurrent_requests fcr
, fnd_concurrent_programs_tl fcpt
, fnd_user fu
, fnd_conc_release_classes fcrc
where fcr.phase_code = 'P'
and fcr.status_code in ('I','Q')
and fcr.program_application_id = fcpt.application_id
and fcr.concurrent_program_id = fcpt.concurrent_program_id
and fcpt.language = 'US'
and fcr.requested_by = fu.user_id
and fcr.release_class_app_id = fcrc.application_id
and fcr.release_class_id = fcrc.release_class_id
and fu.end_date <= sysdate
order by fcr.requested_start_date desc;

#####################################################################
######## Runaway concurrent requests (with no start date )
######################################################################

select a.REQUEST_ID,b.program,to_char(b.ACTUAL_START_DATE,'dd/mm/yy hh24:mi:ss') Time, a.status_code Status,a.phase_code Phase,
c.USER_NAME
from apps.fnd_concurrent_requests a,apps.FND_CONC_REQ_SUMMARY_V b,apps.fnd_user c
where a.request_id=b.request_id and c.USER_ID=a.REQUESTED_BY and  a.phase_code='R' and b.ACTUAL_START_DATE is NULL
 order by 3;
###############################################################

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

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

##############################################################
Parameters used and completion  time of program
##############################################################

set lines 20000
col PROGRAM for a70
set pagesize 100
col ARGUMENT_TEXT for a50
select a.REQUEST_ID,b.program,a.ARGUMENT_TEXT,decode(a.status_code,'R','Normal','I','Normal','Z','Waiting','D','Cancelled','U','Disabled','E','Error','M','NoManager','C','Normal','H','On-Hold','W','Paused','B','Resuming','P','Scheduled','Q','Standby','S','Suspended','X','Terminated','T','Terminating','A','Waiting','G','Warning') Status,
decode(a.phase_code,'P','Pending','R','Running','C','Completed','I','Inactive') Phase,
to_char(b.ACTUAL_START_DATE,'dd/mm/yy hh24:mi:ss') "Start Time",
to_char(b.ACTUAL_COMPLETION_DATE,'dd/mm/yy hh24:mi:ss') "End Time" ,3600*(b.ACTUAL_COMPLETION_DATE-b.ACTUAL_START_DATE) "Execution Time"
from apps.fnd_concurrent_requests a,apps.FND_CONC_REQ_SUMMARY_V b where a.request_id=b.request_id and A.request_id in ('608397')    ;


##################################################################################

Check run time of concurrent program of specific date or for some days duration.
###################################################################################

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;


#############################################################################
Find the terminated requests by users.
################################################################################

SELECT actual_completion_date TERMINATED,
       request_id,
       SUBSTR(program,1,53) PROGRAM,
       SUBSTR(requestor,1,15) REQUESTOR,
       parent_request_id PARENT,
       completion_text MESSAGE
FROM   apps.FND_CONC_REQ_SUMMARY_V
WHERE  STATUS_CODE = 'E'
AND    actual_completion_date > SYSDATE - 2
--AND    requestor LIKE ('%AR_BTOGUSER%')
ORDER BY REQUEST_ID DESC;

##########################################################################333
Check Concurrent Manager and Program rules:
##############################################################################33


--Gives Detail of the Concurrent_queue_name and User_concurrent_program_name

SELECT b.concurrent_queue_name, c.user_concurrent_program_name
FROM FND_CONCURRENT_QUEUE_CONTENT a, fnd_concurrent_queues b, fnd_concurrent_programs_vl c
WHERE a.queue_application_id = 283
and a.concurrent_queue_id = b.concurrent_queue_id
and a.type_id = c.concurrent_program_id
order by decode(INCLUDE_FLAG, 'I', 1, 2), type_code;



################################################################################
Check Scheduled concurrent requests
#####################################################################################
Lot of times we need to find out the concurrent programs scheduled. Users can schedule the concurrent requests in three ways (To run once at a specified time / To run periodically / To run on specific days of the month or week).

The below query will return all the concurrent requests which are scheduled using any of the above methods:
SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:' || cr.description,
cp.user_concurrent_program_name
) NAME,
argument_text, cr.resubmit_interval,
NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
) schedule_type,
DECODE (NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
),
'PERIODICALLY', 'EVERY '
|| cr.resubmit_interval
|| ' '
|| cr.resubmit_interval_unit_code
|| ' FROM '
|| cr.resubmit_interval_type_code
|| ' OF PREV RUN',
'ONCE', 'AT :'
|| TO_CHAR (cr.requested_start_date, 'DD-MON-RR HH24:MI'),
'EVERY: ' || fcr.class_info
) schedule,
fu.user_name, requested_start_date
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu,
apps.fnd_conc_release_classes fcr
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 fcr.release_class_id(+) = cr.release_class_id
AND fcr.application_id(+) = cr.release_class_app_id;

Note: The "SCHEDULE" column in the above query returns a string of zeros and ones for the requests which are scheduled on specific days of the month or week.

Positions 1 through 31: Specific day of the month.
Position 32: Last day of the month
Positions 33 through 39: Sunday through Saturday


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

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

#####################################################################
Find out What are all concurrent programs are inlcuded/excluded concurrent Managers?
###############################################################################33

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;

############################################################################33
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


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

#############################################################################################3
Checking the duplicated schedules of the same program with the same arguments
#################################################################################################3

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;


############################################################################3

Query to find the executable name associated with the concurrent program
###################################################################################
--input:    Concurrent Program Name
SELECT fcpv.user_concurrent_program_name concurrent_program_name
            ,fe.execution_file_name source_name
            ,fl.meaning program_type
   FROM apps.fnd_concurrent_programs_vl fcpv
            ,apps.fnd_executables fe
            ,apps.fnd_lookups fl
 WHERE fe.executable_id=fcpv.executable_id
      AND fl.lookup_type='CP_EXECUTION_METHOD_CODE'
      AND fl.lookup_code=fe.execution_method_code
      AND fcpv.user_concurrent_program_name like 'Refresh iSupport Materialized View';
 
#################################################################################  
Query to find the responsibilities which can run the concurrent program
###############################################################################

--input:     Concurrent Program name

SELECT frv.responsibility_name
   FROM apps.fnd_request_group_units frgu
            ,apps.fnd_responsibility_vl frv
            ,apps.fnd_concurrent_programs_vl fcpv
WHERE  frv.REQUEST_GROUP_ID=frgu.REQUEST_GROUP_ID 
     AND fcpv.concurrent_program_id=frgu.REQUEST_UNIT_ID 
     AND nvl(frv.end_date,sysdate+1)>sysdate 
     AND EXISTS(
                SELECT 'Y'
                   FROM apps.fnd_user_resp_groups grps 
                WHERE grps.responsibility_id=frv.responsibility_id 
                     AND nvl(grps.end_date,sysdate+1)>sysdate
            )
    AND fcpv.user_concurrent_program_name like '%'||:Program_Name||'%'

#############################################################################33
Query to find the last run date of a Concurrent Program
##############################################################################


--input:     Concurrent Program name
SELECT max(fcr.REQUEST_DATE)
   FROM fnd_concurrent_requests          fcr
            ,fnd_concurrent_programs_tl       fcpt
WHERE fcr.concurrent_program_id = fcpt.concurrent_program_id
     AND fcr.program_application_id = fcpt.application_id
     AND fcr.actual_start_date > SYSDATE - :days_to_check   
     AND fcpt.user_concurrent_program_name like '%'||:Program_Name||'%'


#########################################################
Query to find Running request  in Concurrent Manager

###############################################################
set pages 58
set linesize 79Column Reqst Format 999999
Column Requestor Format A10
Column Orcl Format A7
Column Program Format A10
Column Started Format A14
Column Manager Format A11
Column LN Format a10Column Reqst HEADING 'Request|ID '
Column Requestor HEADING 'Requestor'
Column Orcl HEADING 'Oracle|Name'
Column Started HEADING 'Started at'
Column MANAGER HEADING 'Controlling|Manager'
Column LN HEADING 'Logfile|name'
Column Program HEADING 'Program'
select Request_Id Reqst, User_Name Requestor, Oracle_Username Orcl,
Fcr.Logfile_Name LN,
Concurrent_Queue_Name Manager,
Concurrent_Program_Name Program,
To_Char(Actual_Start_Date, 'Mm-Dd-Yy Hh24:Mi') Started,
Run_Alone_Flag, Single_Thread_Flag
From Fnd_Concurrent_Requests Fcr, Fnd_Concurrent_Programs Fcp,
Fnd_Oracle_Userid O, Fnd_Concurrent_Processes P,
Fnd_Concurrent_Queues Q, Fnd_User
Where
Controlling_Manager = Concurrent_Process_ID
And ( P.Concurrent_Queue_ID = Q.Concurrent_Queue_ID AND
P.Queue_Application_ID = Q.Application_ID )
And O.Oracle_Id = Fcr.Oracle_Id
And ( Fcr.Program_Application_Id = Fcp.Application_Id
And Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id )
And Requested_By = User_Id
And Phase_Code = 'R' and Status_Code = 'R'
Order By Actual_Start_Date, Request_Id;



#######################################################################33
LIST ALL THE REGISTERED CONCURRENT PROGRAMS BY MODULE:
##########################################################################3


set lines 180
set pages 300
col SHORT_NAME for a10
col APPLICATION_NAME for a30
SELECT SUBSTR(a.application_name,1,60) Application_NAME
, b.application_short_name SHORT_NAME
, DECODE(SUBSTR(cp.user_concurrent_program_name,4,1),':'
, 'Concurrent Manager Executable'
, 'Subprogram or Function') TYPE
, SUBSTR(d.concurrent_program_name,1,16) PROGRAM
, SUBSTR(cp.user_concurrent_program_name,1,55) USER_PROGRAM_NAME
FROM applsys.FND_CONCURRENT_PROGRAMS_TL cp, applsys.FND_CONCURRENT_PROGRAMS d, applsys.FND_APPLICATION_TL a, applsys.fnd_application b
WHERE cp.application_id = a.application_id
AND d.CONCURRENT_PROGRAM_ID = cp.CONCURRENT_PROGRAM_ID
AND a.APPLICATION_ID = b.APPLICATION_ID
AND b.application_short_name LIKE UPPER('PA')
UNION ALL
SELECT SUBSTR(a.application_name,1,60) c1
, b.application_short_name c2 , 'Form Executable' c3
, SUBSTR(f.form_name,1,16) c4 ,
SUBSTR(d.user_form_name,1,55) c5
FROM applsys.fnd_form f , applsys.FND_APPLICATION_TL a, applsys.fnd_application b, applsys.FND_FORM_TL d
WHERE f.application_id = a.application_id
AND d.FORM_ID = f.FORM_ID
AND a.APPLICATION_ID = b.APPLICATION_ID
AND b.application_short_name LIKE UPPER('PA') ORDER BY 1,2,3,4;



###########################################################333
PROGRAMS RAN MORE 200 TIMES IN A DAY:
###############################################################

SET LINES 120
SET PAGES 900
COL program FORMAT a70
COL cnt FORMAT 999999 HEADING "Number of Runs"
ttitle 'Programs that ran for more than 200 times ' skip 2
SELECT SUBSTR(user_concurrent_program_name,1,70) program
, COUNT(*) cnt
FROM apps.fnd_conc_req_summary_v
WHERE TRUNC(actual_start_date) = TRUNC(SYSDATE) -1
GROUP BY SUBSTR(user_concurrent_program_name,1,70)
HAVING COUNT(*) > 200
ORDER BY 2


###################################################################
Run history of concurrent program
#####################################################################
Cl03vr

SELECT distinct t.user_concurrent_program_name "Conc Program Name",
 r.REQUEST_ID "Request ID",
 to_char(r.ACTUAL_START_DATE,'dd-MON-yy hh24:mi:ss') "Started at",
 to_char(r.ACTUAL_COMPLETION_DATE,'dd-MON-yy hh24:mi:ss') "Completed at",
 decode(r.PHASE_CODE,'C','Completed','I','Inactive','P','Pending','R','Running','NA') "Phasecode",
 decode(r.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') "Status",r.argument_text "Parameters",
 u.user_name "Username",
 --ROUND ((v.actual_completion_date - v.actual_start_date) * 1440,
 --              2
  --            ) "Runtime (in Minutes)"
 round(((nvl(v.actual_completion_date,sysdate)-v.actual_start_date)*24*60),2) "ElapsedTime(Mins)",
 r.LOGFILE_NAME,
 r.OUTFILE_NAME
 FROM
 apps.fnd_concurrent_requests r ,
 apps.fnd_concurrent_programs p ,
 apps.fnd_concurrent_programs_tl t,
 apps.fnd_user u, apps.fnd_conc_req_summary_v v
 WHERE
 r.CONCURRENT_PROGRAM_ID = p.CONCURRENT_PROGRAM_ID
 AND r.actual_start_date >= (sysdate - &NO_DAYS)
 --AND r.requested_by=22378
 AND   r.PROGRAM_APPLICATION_ID = p.APPLICATION_ID
 AND t.concurrent_program_id=r.concurrent_program_id
 AND r.REQUESTED_BY=u.user_id
 AND v.request_id=r.request_id
 --AND r.request_id ='2260046' in ('13829387','13850423')
 and t.user_concurrent_program_name like '%ETL%'
 order by to_char(r.ACTUAL_COMPLETION_DATE,'dd-MON-yy hh24:mi:ss') desc;



 #######################################################################################
 --Query to find the executable name associated with the concurrent program
--input:    Concurrent Program Name
###########################################################################################
SELECT fcpv.user_concurrent_program_name concurrent_program_name
            ,fe.execution_file_name source_name
            ,fl.meaning program_type
   FROM apps.fnd_concurrent_programs_vl fcpv
            ,apps.fnd_executables fe
            ,apps.fnd_lookups fl
 WHERE fe.executable_id=fcpv.executable_id
      AND fl.lookup_type='CP_EXECUTION_METHOD_CODE'
      AND fl.lookup_code=fe.execution_method_code
      AND fcpv.user_concurrent_program_name like 'Refresh iSupport Materialized View';
 
 

 #######################################################################################  
 
--Query to find the concurrent program associated with an executable
--input:    Executable name

 #######################################################################################
SELECT fcpv.user_concurrent_program_name concurrent_program_name
            ,fe.execution_file_name source_name
            ,fl.meaning program_type
   FROM apps.fnd_concurrent_programs_vl fcpv
            ,apps.fnd_executables fe
            ,apps.fnd_lookups fl
 WHERE fe.executable_id=fcpv.executable_id
      AND fl.lookup_type='CP_EXECUTION_METHOD_CODE'
      AND fl.lookup_code=fe.execution_method_code
      AND fe.execution_file_name like '% Program Name%'; 
 
 #######################################################################################  
--Query to find the Concurrent Requests which are incompatible with Request
--input:    Concurrent program name
 #######################################################################################

SELECT fcpt.user_concurrent_program_name Input_Program,
            (SELECT fcpt1.user_concurrent_program_name Incompatible_Programs
                FROM fnd_concurrent_programs_tl   fcpt1
             WHERE fcpt1.concurrent_program_id in (fcps.to_run_concurrent_program_id)
                  AND rownum = 1) Incompatible_Programs
   FROM fnd_concurrent_program_serial fcps
            ,fnd_concurrent_programs_tl       fcpt     
WHERE fcps.running_concurrent_program_id = fcpt.concurrent_program_id
     AND fcpt.user_concurrent_program_name like '%Program_Name%';
     
 #######################################################################################
--Query to find if a concurrent request is parent or not
--input:     Concurrent request ID
 #######################################################################################

SELECT NVL(fcr.has_sub_request,'N') IS_PARENT
   FROM fnd_concurrent_requests fcr
WHERE fcr.request_id = :Request_ID;


 #######################################################################################
--Query to find the last successful run date of a Concurrent Program
--input:     Concurrent Program name

 #######################################################################################


SELECT max(fcr.REQUEST_DATE) last_successfull_run_date
  FROM fnd_concurrent_requests          fcr
      ,fnd_concurrent_programs_tl       fcpt
 WHERE fcr.concurrent_program_id = fcpt.concurrent_program_id
   AND fcr.program_application_id = fcpt.application_id
   AND fcr.status_code in ('C','I','R')
   AND fcr.phase_code = 'C'
   AND fcr.actual_start_date > SYSDATE - :days_to_check 
   AND fcpt.user_concurrent_program_name like '%'||:Program_Name||'%';
 
 ####################################################################################### 
 --Query to find the Request set of a Concurrent Program
--input:    Concurrent Program name
 #######################################################################################
SELECT fcpt.user_concurrent_program_name PROGRAM
            ,frst.USER_REQUEST_SET_NAME REQUEST_SET
   FROM fnd_request_sets_tl                      frst
            ,fnd_request_set_programs         frsp
            ,fnd_concurrent_programs_tl   fcpt
 WHERE frst.request_set_id = frsp.request_set_id
      AND fcpt.application_id = frsp.program_application_id
      AND fcpt.concurrent_program_id = frsp.concurrent_program_id
      AND fcpt.user_concurrent_program_name like '%'||:Program_Name||'%'

 
 
####################################################################################
-------------------- Display the Duration of a particular request for the last 1 week -----------------
############################################################################################

SELECT   fcr.request_id, ftp.user_concurrent_program_name, fcu.user_name,
          ROUND((actual_completion_date - actual_start_date)*24*60,2)minutes, argument_text,actual_start_date,
         actual_completion_date
    FROM fnd_concurrent_requests fcr,
         fnd_concurrent_programs_tl ftp,
         fnd_user fcu
   WHERE fcu.user_id = fcr.requested_by
     AND user_concurrent_program_name = 'Gather Schema Statistics'
     AND actual_completion_date > SYSDATE - 7
     AND fcr.concurrent_program_id = ftp.concurrent_program_id
  ORDER BY actual_completion_date DESC