select l1.sid, ' IS BLOCKING ', l2.sid from gv$lock l1, gv$lock l2 where l1.block = 1 and l2.request > 0 and l1.id1 = l2.id1 and l1.id2 = l2.id2;
--alter system kill session '4552,4528';
select
sid,
serial #,
username,
schemaname,
machine,
program,
logon_time,
status,
module,
sql_id,
action,
client_identifier
from
gv$session
where
sid in
(
'4552',
'',
''
)
;
select
sid,
serial #,
username,
schemaname,
machine,
program,
logon_time,
status,
module,
sql_id,
action,
client_identifier
from
gv$session
where
status = 'ACTIVE';
select
sid,
serial #,
username,
schemaname,
machine,
program,
logon_time,
status,
module,
sql_id,
action,
client_identifier
from
gv$session
where
status = 'ACTIVE'
and username = 'APPS_QUERY';
select
sql_text
from
v $ sqltext
where
sql_id in
(
'3sc3wa7zhw2vn',
''
)
;
select
sql_text,
sql_fulltext,
module,
action
from
gv$sql
where
sql_id in
(
'2r68fjxqkmpt8',
''
)
;
select
*
from
gv$session
where
program like '%toad%';
SELECT
s.SID,
s.serial #,
t.sql_fulltext,
t.sql_id,
s.sql_hash_value,
t.hash_value
FROM
gv$session s,
gv$sql t
where
s.sql_address = t.address
AND s.sql_hash_value = t.hash_value
and t.sql_id = '497wh6n7hu14f';
select
*
from
gv$active_session_history
where
session_id in
(
'5683',
''
)
and to_char(SQL_EXEC_START, 'DD-MM-RR HH24:MI:SS') >= '19-11-15 07:00:00'
and to_char(SQL_EXEC_START, 'DD-MM-RR HH24:MI:SS') <= '19-11-15 09:00:00';
SELECT
a.object_name,
b.process,
b.session_id
FROM
all_objects a,
v $ locked_object b
WHERE
a.object_id = b.object_id;
SELECT
a.request_id,
d.sid,
d.serial #,
d.osuser,
d.process,
c.SPID,
a.REQUESTED_START_DATE,
d.PROGRAM,
a.REQUEST_TYPE,
a.OUTCOME_PRODUCT,
a.REQUESTED_BY,
a.REQUEST_DATE
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 = & requestid;
select
p.PID,
p.SPID,
s.SID
from
gv$process p,
gv$session s
where
s.paddr = p.addr
and s.sid = & sid;
select
sid,
spid
from
v $ process a,
v $ session b
where
a.addr = b.paddr
and spid in
(
'12805',
'12827',
'28526',
'18406'
)
;
select
f.request_id,
v.spid,
s.sid,
s.username,
s.serial #,
s.osuser,
s.status
from
gv$process v,
gv$session s,
applsys.fnd_concurrent_requests f
where
1 = 1 --and s.username=&USER_NAME
and s.paddr = v.addr
and f.oracle_process_id = v.spid
and trunc(f.request_date) = trunc(sysdate)
and sid =& SID;
select
*
from
fnd_user
where
description like '%shetty%';
select
*
from
dba_indexes
where
table_name like 'AP%INVOICES%ALL';
SELECT distinct
fcr.request_id "Request ID",
substr(fcr.program, 1, 40) "Program",
fcr.phase "Phase",
fcr.status "Status",
fcr.ARGUMENT_TEXT "Parameter",
to_char(fcr.actual_start_date, 'DD-MON-RR HH24:MI:SS') "Start",
to_char(fcr.actual_completion_date, 'DD-MON-RR HH24:MI:SS') "
End
",
ROUND( ( NVL( fcr.actual_completion_date, sysdate ) - fcr.actual_start_date ) *60*24, 2 ) "Time(MM:SS)",
fcr.user_name "User Name"
FROM
apps.fnd_amp_requests_v fcr,
fnd_concurrent_queues_tl fcqtl
WHERE
Phase_code = 'R' --and fcr.USER_CONCURRENT_PROGRAM_NAME like '%Posting: Single Ledger%'
--and fcr.ARGUMENT_TEXT like '%A01, 101, 101, , , , 4644,%'
--and fcr.request_id='
and ROUND( ( NVL( fcr.actual_completion_date, sysdate ) - fcr.actual_start_date ) *60*24, 2 ) > 0
order by
8 desc;
select
count(1)
from
fnd_amp_requests_v
where
phase_code = 'C'
and PROGRAM like 'India%Receiving Transaction Processor%'
and to_char(actual_completion_date, 'DD-MM-YYYY HH24:MI:SS') >= '30-09-2015 18:30:00';
select
obj.owner,
obj.object_name,
obj.OBJECT_TYPE,
sess.sid,
sess.serial #,
sess.status,
sess.LOGON_TIME,
sess.module,
sess.sql_id,
sess.ACTION,
sess.process
from
gv$locked_object lo,
dba_objects obj,
gv$session sess
where
obj.object_id = lo.OBJECT_ID
and sess.sid = & sid;
SELECT
fu.user_name "User Name",
frt.responsibility_name "Responsibility Name",
furg.start_date "Start Date",
furg.end_date "
End
Date",
fr.responsibility_key "Responsibility Key",
fa.application_short_name "Application Short Name"
FROM
fnd_user_resp_groups_direct furg,
applsys.fnd_user fu,
applsys.fnd_responsibility_tl frt,
applsys.fnd_responsibility fr,
applsys.fnd_application_tl fat,
applsys.fnd_application fa
WHERE
furg.user_id = fu.user_id
AND furg.responsibility_id = frt.responsibility_id
AND fr.responsibility_id = frt.responsibility_id
AND fa.application_id = fat.application_id
AND fr.application_id = fat.application_id
AND frt.language = USERENV('LANG')
AND UPPER(fu.user_name) = UPPER('11012205') -- <change it>
-- AND (furg.end_date IS NULL OR furg.end_date >= TRUNC(SYSDATE))
ORDER BY
frt.responsibility_name;
SELECT
TIME,
user_name,
responsibility_name,
user_form_name
FROM
apps.fnd_form_sessions_v
ORDER BY
1 ;
SELECT
frt.responsibility_name,
frg.request_group_name,
frgu.request_unit_type,
frgu.request_unit_id,
fcpt.user_concurrent_program_name
FROM
fnd_Responsibility fr,
fnd_responsibility_tl frt,
fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_concurrent_programs_tl fcpt
WHERE
frt.responsibility_id = fr.responsibility_id
AND frg.request_group_id = fr.request_group_id
AND frgu.request_group_id = frg.request_group_id
AND fcpt.concurrent_program_id = frgu.request_unit_id
AND fcpt.user_concurrent_program_name = '&concprogram'
ORDER BY
1,
2,
3,
4;
Select
B.user_concurrent_program_name,
C.user_name,
D.responsibility_name,
A.*
from
apps.fnd_concurrent_requests A,
apps.fnd_concurrent_programs_tl B,
Apps.fnd_user C,
apps.fnd_responsibility_tl D
where
1 = 1
and A.request_id = '43950440'
and B.concurrent_program_id = A.concurrent_program_id
and A.requested_by = C.user_id
and A.responsibility_id = D.responsibility_id
and b.language = userenv('LANG')
and d.language = userenv('LANG')
order by
request_date desc;
Select
'The ' || Concurrent_Queue_Name || ' concurrent manager ran your request from',
to_char(Actual_Start_date, ' MON-DD-YY HH:MI:SS AM') || ' - to - ' || to_char(Actual_COMPLETION_date, 'MON-DD-YY HH:MI:SS AM'),
'The ' || Concurrent_Queue_Name || ' concurrent manager log file is ' || P.Logfile_Name,
'Request log file is ' || R.Logfile_Name
From
Fnd_Concurrent_Queues Q,
Fnd_Concurrent_requests R,
Fnd_Concurrent_Processes P
Where
(
P.Concurrent_Queue_ID = Q.Concurrent_Queue_ID
And Queue_Application_ID = Q.Application_ID
)
And R.Controlling_Manager = P.Concurrent_Process_ID
And R.Phase_Code = 'C'
And Request_ID = & Request_ID;
select
request_id,
Program,
phase,
status,
argument_text,
to_char(actual_start_date, 'DD-MON-RR HH24:MI:SS') "Start",
to_char(actual_completion_date, 'DD-MON-RR HH24:MI:SS') "
End
",
ROUND( ( NVL( actual_completion_date, sysdate ) - actual_start_date ) *60*24, 2 ) "Time(MM:SS)",
user_name "User Name"
from
apps.fnd_amp_requests_v a
where
a.phase_code = 'R' --and ROUND( ( NVL( actual_completion_date, sysdate ) - actual_start_date ) *60*24, 2 ) > 15
and rownum not in
(
select
max(rownum)
from
apps.fnd_amp_requests_v b
where
a.program = b.program
and a.argument_text = b.argument_text
and phase_code = 'R'
group by
b.argument_text
)
;
select
*
from
(
select
request_id,
Program,
phase,
status,
argument_text,
to_char(actual_start_date, 'DD-MON-RR HH24:MI:SS') "Start",
to_char(actual_completion_date, 'DD-MON-RR HH24:MI:SS') "
End
",
ROUND( ( NVL( actual_completion_date, sysdate ) - actual_start_date ) *60*24, 2 ) "Time(MM:SS)",
user_name "User Name",
count(*) over (partition by argument_text, program) ct
from
apps.fnd_amp_requests_v f
where
phase_code = 'R'
)
where
ct > 1 ;
select
a.request_id,
x.Program,
a.argument_text,
x.user_name
from
fnd_concurrent_requests a,
fnd_amp_requests_v x
where
a.phase_code = 'R'
and a.request_id = x.request_id
and rownum not in
(
select
max(rownum)
from
fnd_concurrent_requests b
where
a.argument_text = b.argument_text
and phase_code = 'R'
group by
b.argument_text
)
;
select
POOL,
round(bytes / 1024 / 1024, 0) FREE_MB
from
gv$sgastat
where
name like '%free memory%';
select
lg.group #,
lg.bytes / 1024 / 1024 mb,
lg.status,
lg.archived,
lf.member
from
gv$logfile lf,
gv$log lg
where
lg.group # = lf.group #
order by
1,
2;
OPP log for a request ID and its path: SELECT fcpa.concurrent_request_id request_id, fcp.node_name node_name, fcp.logfile_name logfile_path 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 = &REQUEST_ID;
Patching details:
select
decode(a.patch_name, 'merged', d.bug_number, a.patch_name) Patch_Number,
b.patch_driver_id,
b.last_update_date,
nvl(b.patch_abstract, c.patch_abstract)
from
ad_applied_patches a,
ad_patch_drivers b,
ad_comprising_patches c,
ad_bugs d
where
a.creation_date > (sysdate - 15)
and a.applied_patch_id = b.applied_patch_id
and b.patch_driver_id = c.patch_driver_id ( + )
and c.bug_id = d.bug_id ( + )
order by
3 desc;
Concurrent req with error:
SELECT f.user_name, r.request_id, prg.concurrent_program_name Module, p.USER_CONCURRENT_PROGRAM_NAME, decode(r.phase_code, 'C', 'Completed', 'I', 'Inactive', 'P', 'Pending', 'R', 'Running', r.phase_code) Phase_Desc, 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', r.status_code) Status_Desc, r.actual_start_date, r.actual_completion_date FROM fnd_concurrent_requests r, fnd_concurrent_programs_tl p, fnd_concurrent_programs prg, fnd_user f WHERE r.program_APPLICATION_ID = p.APPLICATION_ID and r.CONCURRENT_PROGRAM_ID = p.CONCURRENT_PROGRAM_ID and r.requested_by = f.user_id and r.program_APPLICATION_ID = prg.APPLICATION_ID and r.CONCURRENT_PROGRAM_ID = prg.CONCURRENT_PROGRAM_ID and r.status_code in ( 'E', 'G' ) and trunc(r.actual_completion_date) > = trunc(sysdate - 1) order by actual_start_date desc; select * from fnd_amp_requests_v where user_name = 'ALGAHTXF02ENT' and program like 'ALLANASONS FIN : Sales Register Report New GST'; select * from fnd_amp_requests_v where user_name = 'ALGAHTXF02ENT' and program like 'Allanasons FIN : Purchase Register Report New - GST'; select * from fnd_conc_pp_actions where concurrent_request_id = '72388165' and action_type = 7;
/*Load Query with active running session and SQLS*/
SELECT DISTINCT ( SELECT trunc(VALUE, 2) FROM v$osstat WHERE stat_name = 'LOAD') load, s.username, TO_CHAR (TRUNC (LAST_CALL_ET / 3600), 'FM9900') || ':' || TO_CHAR (TRUNC (MOD (LAST_CALL_ET, 3600) / 60), 'FM00') || ':' || TO_CHAR (MOD (LAST_CALL_ET, 60), 'FM00') "HH:MM:SS", spid, sw.sid, s.sql_id, S.event, s.wait_class, sql_text, s.module, s.program, s.machine, s.ROW_WAIT_OBJ# FROM gv$session_wait sw, gv$sql q, gv$process p, gv$session s WHERE sw.sid = s.sid AND s.paddr = p.addr AND s.sql_HASH_VALUE = q.HASH_VALUE AND s.status = 'ACTIVE' AND s.USERNAME IS NOT NULL ORDER BY 3 DESC;
/*Workflow status*/
select SC.COMPONENT_TYPE, SC.COMPONENT_NAME, FND_SVC_COMPONENT.Get_Component_Status(SC.COMPONENT_NAME) COMPONENT_STATUS from FND_SVC_COMPONENTS SC where component_type = 'WF_MAILER' order by 1 desc, 2;
/*Workflow Mailer Logfile*/
SQL> SELECT concurrent_queue_name manager, fcp.last_update_date,
fcp.concurrent_process_id pid, meaning, fcp.logfile_name
FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp, fnd_lookups
flkup
WHERE concurrent_queue_name = 'WFMLRSVC'
AND fcq.concurrent_queue_id = fcp.concurrent_queue_id
AND fcq.application_id = fcp.queue_application_id
AND flkup.lookup_code=fcp.process_status_code
AND lookup_type ='CP_PROCESS_STATUS_CODE'
AND meaning='Active'
SQL> select fl.meaning,fcp.process_status_code,
decode(fcq.concurrent_queue_name,'WFMLRSVC','maile r container','WFALSNRSVC','listener container',fcq.concurrent_queue_name),
fcp.concurrent_process_id,os_process_id, fcp.logfile_name
from fnd_concurrent_queues fcq, fnd_concurrent_processes fcp , fnd_lookups fl
where fcq.concurrent_queue_id=fcp.concurrent_queue_id and fcp.process_status_code='A'
and fl.lookup_type='CP_PROCESS_STATUS_CODE' and
fl.lookup_code=fcp.process_status_code
and concurrent_queue_name in('WFMLRSVC','WFALSNRSVC')
order by fcp.logfile_name;
/*Workflow Version*/
select text from WF_RESOURCES where name = 'WF_VERSION';
/*Check if workflow mails are getting sent*/
select * from ( select NOTIFICATION_ID, MESSAGE_NAME, status, mail_status, begin_date, sent_date from wf_notifications where begin_date > sysdate - 1 and status <> 'CLOSED' and mail_status is not null order by begin_date desc)
where rownum < 21;
/*Check if important managers are running*/
SELECT DECODE(CONCURRENT_QUEUE_NAME, 'FNDICM', 'Internal Manager', 'FNDCRM', 'Conflict Resolution Manager', 'AMSDMIN', 'Marketing Data Mining Manager', 'C_AQCT_SVC', 'C AQCART Service', 'FFTM', 'FastFormula Transaction Manager', 'FNDCPOPP', 'Output Post Processor', 'FNDSCH', 'Scheduler/Prereleaser Manager', 'FNDSM_AQHERP', 'Service Manager: AQHERP', 'FTE_TXN_MANAGER', 'Transportation Manager', 'IEU_SH_CS', 'Session History Cleanup', 'IEU_WL_CS', 'UWQ Worklist Items Release for Crashed session', 'INVMGR', 'Inventory Manager', 'INVTMRPM', 'INV Remote Procedure Manager', 'OAMCOLMGR', 'OAM Metrics Collection Manager', 'PASMGR', 'PA Streamline Manager', 'PODAMGR', 'PO Document Approval Manager', 'RCVOLTM', 'Receiving Transaction Manager', 'STANDARD', 'Standard Manager', 'WFALSNRSVC', 'Workflow Agent Listener Service', 'WFMLRSVC', 'Workflow Mailer Service', 'WFWSSVC', 'Workflow Document Web Services Service', 'WMSTAMGR', 'WMS Task Archiving Manager', 'XDP_APPL_SVC', 'SFM Application Monitoring Service', 'XDP_CTRL_SVC', 'SFM Controller Service', 'XDP_Q_EVENT_SVC', 'SFM Event Manager Queue Service', 'XDP_Q_FA_SVC', 'SFM Fulfillment Actions Queue Service', 'XDP_Q_FE_READY_SVC', 'SFM Fulfillment Element Ready Queue Service', 'XDP_Q_IN_MSG_SVC', 'SFM Inbound Messages Queue Service', 'XDP_Q_ORDER_SVC', 'SFM Order Queue Service', 'XDP_Q_TIMER_SVC', 'SFM Timer Queue Service', 'XDP_Q_WI_SVC', 'SFM Work Item Queue Service', 'XDP_SMIT_SVC', 'SFM SM Interface Test Service') AS "Concurrent Manager's Name",max_processes AS "TARGET Processes", running_processes AS "ACTUAL Processes" FROM apps.fnd_concurrent_queues WHERE CONCURRENT_QUEUE_NAME IN ('FNDICM','FNDCPOPP','STANDARD','WFALSNRSVC','WFMLRSVC','WFWSSVC' ) order by 2 desc;
/*Check if all managers are running*/
SELECT DECODE(CONCURRENT_QUEUE_NAME, 'FNDICM', 'Internal Manager', 'FNDCRM', 'Conflict Resolution Manager', 'AMSDMIN', 'Marketing Data Mining Manager', 'C_AQCT_SVC', 'C AQCART Service', 'FFTM', 'FastFormula Transaction Manager', 'FNDCPOPP', 'Output Post Processor', 'FNDSCH', 'Scheduler/Prereleaser Manager', 'FNDSM_AQHERP', 'Service Manager: AQHERP', 'FTE_TXN_MANAGER', 'Transportation Manager', 'IEU_SH_CS', 'Session History Cleanup', 'IEU_WL_CS', 'UWQ Worklist Items Release for Crashed session', 'INVMGR', 'Inventory Manager', 'INVTMRPM', 'INV Remote Procedure Manager', 'OAMCOLMGR', 'OAM Metrics Collection Manager', 'PASMGR', 'PA Streamline Manager', 'PODAMGR', 'PO Document Approval Manager', 'RCVOLTM', 'Receiving Transaction Manager', 'STANDARD', 'Standard Manager', 'WFALSNRSVC', 'Workflow Agent Listener Service', 'WFMLRSVC', 'Workflow Mailer Service', 'WFWSSVC', 'Workflow Document Web Services Service', 'WMSTAMGR', 'WMS Task Archiving Manager', 'XDP_APPL_SVC', 'SFM Application Monitoring Service', 'XDP_CTRL_SVC', 'SFM Controller Service', 'XDP_Q_EVENT_SVC', 'SFM Event Manager Queue Service', 'XDP_Q_FA_SVC', 'SFM Fulfillment Actions Queue Service', 'XDP_Q_FE_READY_SVC', 'SFM Fulfillment Element Ready Queue Service', 'XDP_Q_IN_MSG_SVC', 'SFM Inbound Messages Queue Service', 'XDP_Q_ORDER_SVC', 'SFM Order Queue Service', 'XDP_Q_TIMER_SVC', 'SFM Timer Queue Service', 'XDP_Q_WI_SVC', 'SFM Work Item Queue Service', 'XDP_SMIT_SVC', 'SFM SM Interface Test Service') AS "Concurrent Manager's Name", max_processes AS "TARGET Processes", running_processes AS "ACTUAL Processes" FROM apps.fnd_concurrent_queues WHERE CONCURRENT_QUEUE_NAME IN ('FNDICM','FNDCRM','AMSDMIN','C_AQCT_SVC','FFTM','FNDCPOPP','FNDSCH','FNDSM_AQHERP','FTE_TXN_MANAGER','IEU_SH_CS','IEU_WL_CS','INVMGR','INVTMRPM','OAMCOLMGR','PASMGR','PODAMGR','RCVOLTM','STANDARD','WFALSNRSVC','WFMLRSVC','WFWSSVC','WMSTAMGR','XDP_APPL_SVC','XDP_CTRL_SVC','XDP_Q_EVENT_SVC','XDP_Q_FA_SVC','XDP_Q_FE_READY_SVC','XDP_Q_IN_MSG_SVC','XDP_Q_ORDER_SVC','XDP_Q_TIMER_SVC','XDP_Q_WI_SVC','XDP_SMIT_SVC' ) order by 2 desc;
/*check tablespaces having*/
SELECT df.tablespace_name tspace, df.bytes / (1024 * 1024 * 1024) tot_ts_size, ( df.bytes / (1024 * 1024 * 1024) - SUM (fs.bytes) / (1024 * 1024 * 1024) ) used_ts_size, SUM (fs.bytes) / (1024 * 1024 * 1024) free_ts_size, ROUND (SUM (fs.bytes) * 100 / df.bytes) free_pct, ROUND ( (df.bytes - SUM (fs.bytes)) * 100 / df.bytes) used_pct1 FROM dba_free_space fs, ( SELECT tablespace_name, SUM (bytes) bytes FROM dba_data_files GROUP BY tablespace_name ) df WHERE fs.tablespace_name = df.tablespace_name GROUP BY df.tablespace_name, df.bytes ORDER BY 6 DESC;
select df.tablespace_name "Tablespace", totalusedspace "Used GB", ( df.totalspace - tu.totalusedspace ) "Free MB", df.totalspace "Total GB", 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*1024)) totalusedspace, tablespace_name from dba_segments group by tablespace_name ) tu where df.tablespace_name = tu.tablespace_name order by 5;
/*check for blocking sessions*/
SELECT s1.username, s1.machine, s1.last_call_et, s1.sid "BLOCKING SID", s2.username, s2.machine, s2.sid "BLOCKED SID" FROM gv$lock l1, gv$session s1, gv$lock l2, gv$session s2 WHERE s1.sid = l1.sid AND s2.sid = l2.sid AND l1.BLOCK = 1 AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2 ;
select
l1.sid, ' IS BLOCKING ', l2.sid from gv$lock l1, gv$lock l2 where l1.block = 1 and l2.request > 0 and l1.id1 = l2.id1 and l1.id2 = l2.id2;
/*check long running concurrent requests*/
select a.request_id, c.user_name, c.DESCRIPTION, b.USER_CONCURRENT_PROGRAM_NAME pname, to_char(a.ACTUAL_START_DATE, 'DD-Mon-yy HH24:MI:SS') "Start Time", TRUNC((SYSDATE - a.actual_start_date) * 24*60, 1) "Time in minutes" from fnd_concurrent_requests a, fnd_concurrent_programs_vl b, apps.fnd_user c where a.CONCURRENT_PROGRAM_ID = b.CONCURRENT_PROGRAM_ID and a.phase_code = 'R' and c.user_id = a.REQUESTED_BY and a.REQUESTED_START_DATE is not null and TRUNC((SYSDATE - a.actual_start_date) * 24*60, 1) > = 30;
/*avg run time*/
select requestor, request_id, user_concurrent_program_name, argument_text, request_Date, ACTUAL_START_DATE, ( nvl(ACTUAL_COMPLETION_DATE, sysdate) - ACTUAL_START_DATE ) *1440 "RUNNING_TIME (mins)", ( select avg(nvl(cr.actual_completion_date - cr.actual_start_date, 0))*1440 "avg_run_time" from fnd_concurrent_programs cp, fnd_concurrent_requests cr where cp.CONCURRENT_PROGRAM_ID = cr.CONCURRENT_PROGRAM_ID and cp.APPLICATION_ID = cr.PROGRAM_APPLICATION_ID and cp.CONCURRENT_PROGRAM_ID = crs.CONCURRENT_PROGRAM_ID and cr.PHASE_CODE = 'C' ) avg_run_time from fnd_conc_req_summary_v crs where phase_code = 'R' and status_code = 'R' and request_date > sysdate - 30 and request_date < sysdate order by request_Date;
--Long Running request Query
SELECT a.request_id, a.oracle_process_id "SPID", fu.user_name, 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, ( 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;
--Workflow Component Status
select SC.COMPONENT_TYPE, SC.COMPONENT_NAME, FND_SVC_COMPONENT.Get_Component_Status(SC.COMPONENT_NAME) COMPONENT_STATUS from FND_SVC_COMPONENTS SC order by 1, 2;
####Query to find sid,pid etc ... from Request Id####
SELECT a.request_id, d.sid, d.serial #, d.osuser, d.process, c.SPID 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';
##Temp Usage##
SELECT S.sid, S.serial #, S.username, S.osuser, P.spid, S.module, P.program, SUM (su.blocks) * TBS.block_size / 1024 / 1024 mb_used, su.tablespace FROM v$sort_usage su, gv$session S, dba_tablespaces tbs, gv$process P WHERE su.session_addr = S.saddr AND S.paddr = P.addr AND su.tablespace = tbs.tablespace_name GROUP BY S.sid, S.serial #, S.username, S.osuser, P.spid, S.module, P.program, TBS.block_size, su.tablespace ORDER BY mb_used desc;
LONG RUNNING CONCURRENT REQUESTS : -
SELECT a.request_id, a.oracle_process_id "SPID", fu.user_name, 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, ( 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;
;
SELECT a.request_id, d.sid, d.serial #, d.osuser, d.process, c.SPID 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 = 23462016 AND a.phase_code = 'R';
SELECT fu.user_name "User Name", frt.responsibility_name "Responsibility Name", furg.start_date "Start Date", furg.end_date " End Date", fr.responsibility_key "Responsibility Key", fa.application_short_name "Application Short Name" FROM fnd_user_resp_groups_direct furg, applsys.fnd_user fu, applsys.fnd_responsibility_tl frt, applsys.fnd_responsibility fr, applsys.fnd_application_tl fat, applsys.fnd_application fa WHERE furg.user_id = fu.user_id AND furg.responsibility_id = frt.responsibility_id AND fr.responsibility_id = frt.responsibility_id AND fa.application_id = fat.application_id AND fr.application_id = fat.application_id AND frt.language = USERENV('LANG') AND frt.responsibility_name like '%Admin%' -- AND UPPER(fu.user_name) = UPPER('AMOHSIN') -- <change it> -- AND (furg.end_date IS NULL OR furg.end_date >= TRUNC(SYSDATE)) ORDER BY fu.user_name;
select distinct
a.form_name, a.enabled, c.USER_FORM_NAME, d.APPLICATION_NAME from FND_FORM_CUSTOM_RULES a, FND_FORM b, FND_FORM_TL c, fnd_application_tl d where enabled = 'Y' and a.form_name = b.form_name and b.form_id = c.form_id and b.application_id = d.application_id order by application_name select release_name from fnd_product_groups;SELECT a.request_id, d.sid, d.serial #, d.osuser, d.process, c.SPID 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';
You can use the following query to find the user's who are logged on
SELECT TIME, user_name, responsibility_name, user_form_name FROM apps.fnd_form_sessions_v ORDER BY 1;
Query to list concurrent program details with its parameter, values set and default value/type AND Attached Responsibilities:
1. Query to list all the responsibilities attached to a user:
select fu.user_name, fr.responsibility_name, furg.start_date, furg.end_date from fnd_user_resp_groups_direct furg, fnd_user fu, fnd_responsibility_tl fr where fu.user_name = :user_name and furg.user_id = fu.user_id and furg.responsibility_id = fr.responsibility_id and fr.language = userenv('lang');
2. To find from which responsibility a concurrent program can be run:
select distinct a.user_concurrent_program_name, a.description, request_group_name, e.responsibility_name from fnd_concurrent_programs_tl a, fnd_request_groups b, fnd_request_group_units c, fnd_responsibility d, fnd_responsibility_tl e where a.concurrent_program_id = c.request_unit_id and b.request_group_id = c.request_group_id and b.request_group_id = d.request_group_id and d.responsibility_id = e.responsibility_id and a.application_id = b.application_id and b.application_id = c.application_id and d.application_id = e.application_id and a.concurrent_program_id = :p_conc_program_id;
3. Provide concurrent program name to the following query:
It lists all the request sets which are created with the concurrent program given.
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') ) ) ;
4. Provide the request set name to the following query:
It lists all concurrent programs of this request set.
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') ) ) ;
5. Query to list concurrent program details with its parameter, values set and default value/type:
select fcpl.user_concurrent_program_name, fcp.concurrent_program_name, 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, fdfcuv.flex_value_set_id, ffvs.flex_value_set_name, flv.meaning default_type, fdfcuv.default_value from fnd_concurrent_programs fcp, fnd_concurrent_programs_tl fcpl, fnd_descr_flex_col_usage_vl fdfcuv, fnd_flex_value_sets ffvs, fnd_lookup_values flv, fnd_application_vl fav where fcp.concurrent_program_id = fcpl.concurrent_program_id and fcpl.user_concurrent_program_name = :conc_prg_name and fcpl.language = 'us' and fav.application_id = fcp.application_id and fdfcuv.descriptive_flexfield_name = '$srs$.' || fcp.concurrent_program_name and ffvs.flex_value_set_id = fdfcuv.flex_value_set_id and flv.lookup_type( + ) = 'flex_default_type' and flv.lookup_code( + ) = fdfcuv.default_type and flv.language( + ) = userenv ('lang');
6. query to find out concurrent program details and its parameters
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 fnd_concurrent_programs fcp, fnd_concurrent_programs_tl fcpl, fnd_descr_flex_col_usage_vl fdfcuv, 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' and fdfcuv.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name;
7. For checking the locks in concurrent jobs:
select decode(request, 0, 'holder: ', 'waiter: ') || sid sess, inst_id, id1, id2, lmode, request, type from gv $ lock where ( id1, id2, type ) in ( select id1, id2, type from gv $ lock where request > 0 ) order by id1, request;
8. For checking the concurrent programs running currently with details of processed time-- and start date:
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;
9. For checking last run of a concurrent program along with processed time:
-- useful to find the details of concurrent programs which run daily and comparison purpose
select distinct c.user_concurrent_program_name, round(((a.actual_completion_date - a.actual_start_date)*24*60*60 / 60), 2) as process_time, a.request_id, a.parent_request_id, to_char(a.request_date, 'dd-mon-yy hh24:mi:ss'), to_char(a.actual_start_date, 'dd-mon-yy hh24:mi:ss'), to_char(a.actual_completion_date, 'dd-mon-yy hh24:mi:ss'), ( 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 -- trunc(a.actual_completion_date) = '24-aug-2005' c.user_concurrent_program_name = 'incentive compensation analytics - odi' -- and argument_text like '%, , , , ,%'; -- and status_code!='c';
10. For checking the last run of concurrent program:
- use below query to check all the concurrent request running which may refer given package
-- this is very useful check before compiling any package on given instance.
-- the query can be modified as per requirement.
-- remove fnd_concurrent_requests table and joins to check all program dependent on given package.
select
fcr.request_id,
fcpv.user_concurrent_program_name,
fcpv.concurrent_program_name,
fcpv.concurrent_program_id,
fcr.status_code,
fcr.phase_code
from
fnd_concurrent_programs_vl fcpv,
fnd_executables fe,
sys.dba_dependencies dd,
fnd_concurrent_requests fcr
where
fcpv.executable_id = fe.executable_id
and fe.execution_method_code = 'i'
and substr(fe.execution_file_name, 1, instr(fe.execution_file_name, '.', 1, 1) - 1) = upper(dd.name)
and dd.referenced_type in
(
'view',
'table',
'trigger',
'package'
)
-- add as required
--and referenced_owner = 'xxcus'
and dd.referenced_name = upper('&package_name')
and fcr.concurrent_program_id = fcpv.concurrent_program_id
and fcr.phase_code not in
(
'c',
'p'
)
;
11. The following query will display the time taken to execute the concurrent programs --for a particular user with the latest concurrent programs sorted in least time taken
-- to complete the request. select f.request_id, pt.user_concurrent_program_name user_conc_program_name, f.actual_start_date start_on, f.actual_completion_date end_on, 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, 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_start_date desc
12. By using the below query we can get sid,serial#,spid of the concurrent request:
select a.request_id, d.sid, d.serial #, c.spid from apps.fnd_concurrent_requests a, apps.fnd_concurrent_processes b, v $ process c, v $ session d where a.controlling_manager = b.concurrent_process_id and c.pid = b.oracle_process_id and b.session_id = d.audsid and a.request_id = & request_id and a.phase_code = 'r';
13. By using below concurrent manager and program rules
--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;
14. Gives details of running concurrent jobs:
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;
15. Gives detail of concurrent job completed and pending:
select fcr.request_id, fcpv.user_concurrent_program_name, fcpv.concurrent_program_name, fcpv.concurrent_program_id, fcr.status_code, fcr.phase_code from fnd_concurrent_programs_vl fcpv, fnd_executables fe, sys.dba_dependencies dd, fnd_concurrent_requests fcr where fcpv.executable_id = fe.executable_id and fe.execution_method_code = 'i' and substr(fe.execution_file_name, 1, instr(fe.execution_file_name, '.', 1, 1) - 1) = upper(dd.name) and dd.referenced_type in ( 'view', 'table', 'trigger', 'package' ) -- add as required --and referenced_owner = 'xxcus' and dd.referenced_name = upper('&package_name') and fcr.concurrent_program_id = fcpv.concurrent_program_id and fcr.phase_code not in ( 'c', 'p' ) ;
16. Gives detail of running and completed concurrent jobs with start date and end date
-- latest one at the top
select f.request_id, pt.user_concurrent_program_name user_conc_program_name, f.actual_start_date start_on, f.actual_completion_date end_on, 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, 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_start_date desc;
17. Wait events details related with concurrent programs:
select s.saddr, s.sid, s.serial #, s.audsid, s.paddr, s.user #, s.username, s.command, s.ownerid, s.taddr, s.lockwait, s.status, s.server, s.schema #, s.schemaname, s.osuser, s.process, s.machine, s.terminal, upper (s.program) program, s.type, s.sql_address, s.sql_hash_value, s.sql_id, s.sql_child_number, s.sql_exec_start, s.sql_exec_id, s.prev_sql_addr, s.prev_hash_value, s.prev_sql_id, s.prev_child_number, s.prev_exec_start, s.prev_exec_id, s.plsql_entry_object_id, s.plsql_entry_subprogram_id, s.plsql_object_id, s.plsql_subprogram_id, s.module, s.module_hash, s.action, s.action_hash, s.client_info, s.fixed_table_sequence, s.row_wait_obj #, s.row_wait_file #, s.row_wait_block #, s.row_wait_row #, s.logon_time, s.last_call_et, s.pdml_enabled, s.failover_type, s.failover_method, s.failed_over, s.resource_consumer_group, s.pdml_status, s.pddl_status, s.pq_status, s.current_queue_duration, s.client_identifier, s.blocking_session_status, s.blocking_instance, s.blocking_session, s.seq #, s.event #, s.event, s.p1text, s.p1, s.p1raw, s.p2text, s.p2, s.p2raw, s.p3text, s.p3, s.p3raw, s.wait_class_id, s.wait_class #, s.wait_class, s.wait_time, s.seconds_in_wait, s.state, s.wait_time_micro, s.time_remaining_micro, s.time_since_last_wait_micro, s.service_name, s.sql_trace, s.sql_trace_waits, s.sql_trace_binds, s.sql_trace_plan_stats, s.session_edition_id, s.creator_addr, s.creator_serial # from v$session s where ( (s.username is not null) and ( nvl (s.osuser, 'x') <> 'system' ) and ( s.type <> 'background' ) and status = 'active' ) order by "program";
18. To find the pid of the concurrent job:
select a.inst_id, sid, b.spid from gv$session a, gv$process b, apps.fnd_concurrent_requests c where a.paddr = b.addr and request_id = '31689665' and a.inst_id = b.inst_id and c.os_process_id = a.process;
19 :to find the database sid of the concurrent job
-- we need our concurrent request id as an input.
-- c.spid= is the operating system process id
-- d.sid= is the oracle process id
column process heading "fndlibr pid" select a.request_id, d.sid, d.serial #, d.osuser, d.process, c.spid from apps.fnd_concurrent_requests a, apps.fnd_concurrent_processes b, v$process c, v$session d where a.controlling_manager = b.concurrent_process_id and c.pid = b.oracle_process_id and b.session_id = d.audsid and a.request_id = & request_id and a.phase_code = 'r';
To decrypt Application Front end Users Password :
select usr.user_name, get_pwd.decrypt ( ( SELECT ( SELECT get_pwd.decrypt(FND_WEB_SEC.GET_GUEST_USERNAME_PWD, usertable.encrypted_foundation_password) FROM DUAL) AS apps_password FROM fnd_user usertable WHERE usertable.user_name = ( SELECT substr(FND_WEB_SEC.GET_GUEST_USERNAME_PWD, 1, instr(FND_WEB_SEC.GET_GUEST_USERNAME_PWD, '/') - 1) from dual ) ), usr.ENCRYPTED_USER_PASSWORD) password from fnd_user usr where usr.user_name = '&username';
To get the apps Password in EBS:
SELECT ( SELECT get_pwd.decrypt (fnd_web_sec.get_guest_username_pwd, usertable.encrypted_foundation_password ) FROM DUAL) AS apps_password FROM fnd_user usertable WHERE usertable.user_name = ( SELECT SUBSTR (fnd_web_sec.get_guest_username_pwd, 1, INSTR (fnd_web_sec.get_guest_username_pwd, '/') - 1 ) FROM DUAL ) ;
The below query will return all the concurrent requests which are scheduled:
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;
PS: 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
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.
PS: 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;
Average pending time per request:
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;
PS: Depending on the purging schedules some requests might miss if the corresponding data in fnd_concurrent_processes is purged.
/* Tablespace monitoring */
select t.tablespace_name "TABLESPACE", t.TOTAL "TOTAL SIZE", nvl(f.FREE, 0) "FREE SPACE", 100 - round(nvl(f.FREE, 0)*100 / t.TOTAL) " % used", round(nvl(f.FREE, 0)*100 / t.TOTAL) " % FREE" FROM ( select tablespace_name, trunc(sum(bytes) / 1024 / 1024) as "TOTAL" from dba_data_files group by tablespace_name ) t, ( select tablespace_name, trunc(sum(bytes) / 1024 / 1024) as "FREE" from dba_free_space group by tablespace_name ) f where t.tablespace_name = f.tablespace_name( + ) order by 5;
/*To check patch applied on both nodes*/
SELECT d.patch_name, b.applications_system_name, b.NAME, c.driver_file_name, a.patch_driver_id, a.patch_run_id, a.session_id, a.patch_top, a.start_date, a.end_date, a.success_flag, a.failure_comments FROM ad_patch_runs a, ad_appl_tops b, ad_patch_drivers c, ad_applied_patches d WHERE a.appl_top_id = b.appl_top_id AND a.patch_driver_id = c.patch_driver_id AND c.applied_patch_id = d.applied_patch_id AND a.patch_driver_id IN ( SELECT patch_driver_id FROM ad_patch_drivers WHERE applied_patch_id IN ( SELECT applied_patch_id FROM ad_applied_patches WHERE patch_name = '18484204' ) ) ORDER BY 3;
/* currently login users */
SELECT DISTINCT icx.session_id, icx.user_id, fu.user_name, fu.description FROM icx_sessions icx, fnd_user fu WHERE disabled_flag != 'Y' AND icx.pseudo_flag = 'N' AND ( last_connect + DECODE (fnd_profile.VALUE ('ICX_SESSION_TIMEOUT'), NULL, limit_time, 0, limit_time, fnd_profile.VALUE ('ICX_SESSION_TIMEOUT') / 60 ) / 24 ) > SYSDATE AND icx.counter < limit_connects AND icx.user_id = fu.user_id -- AND fu.USER_NAME != 'GUEST' ORDER BY 3;
-- Currently login users with responsibility:
SELECT fnd.user_name, fnd.description, icx.responsibility_application_id, icx.responsibility_id, frt.responsibility_name, icx.session_id, icx.first_connect, icx.last_connect, DECODE ((icx.disabled_flag), 'N', 'ACTIVE', 'Y', 'INACTIVE' ) status FROM fnd_user fnd, icx_sessions icx, fnd_responsibility_tl frt WHERE fnd.user_id = icx.user_id AND icx.responsibility_id = frt.responsibility_id AND icx.disabled_flag <> 'Y' AND ( last_connect + DECODE (fnd_profile.VALUE ('ICX_SESSION_TIMEOUT'), NULL, limit_time, 0, limit_time, fnd_profile.VALUE ('ICX_SESSION_TIMEOUT') / 60 ) / 24 ) > SYSDATE ORDER BY icx.last_connect;
/* To check Workflow item /child/parent closed or Open*/
SELECT c.item_type CHILD, DECODE (c.end_date, NULL, 'OPEN', 'CLOSED') child_status, c.parent_item_type PARENT, DECODE (c.parent_item_type, NULL, 'NOPARENT', DECODE (p.end_date, NULL, 'OPEN', 'CLOSED') ) parent_status, COUNT (*) FROM wf_items p, wf_items c WHERE p.item_type( + ) = c.parent_item_type AND p.item_key( + ) = c.parent_item_key GROUP BY c.item_type, DECODE (c.end_date, NULL, 'OPEN', 'CLOSED'), c.parent_item_type, DECODE (c.parent_item_type, NULL, 'NOPARENT', DECODE (p.end_date, NULL, 'OPEN', 'CLOSED') ) ORDER BY c.item_type, c.parent_item_type;
--Node information in more detail:
SELECT node_name, TO_CHAR (creation_date, 'DD-MON-RR HH24:MI') creation_date, platform_code, DECODE (status, 'Y', 'ACTIVE', 'INACTIVE') status, DECODE (support_cp, 'Y', 'ConcMgr', 'No') concmgr, DECODE (support_forms, 'Y', 'Forms', 'No') forms, DECODE (support_web, 'Y', 'Web', 'No') webserver, DECODE (support_admin, 'Y', 'Admin', 'No') ADMIN, DECODE (support_db, 'Y', 'Rdbms', 'No') DATABASE, TO_CHAR (last_monitored_time, 'DD-MON-RR HH24:MI:SS') last_monitored, node_mode, server_address, HOST, domain, webhost, virtual_ip, server_id FROM fnd_nodes WHERE node_name != 'AUTHENTICATION';
/* Currently running concurrent requests */
SELECT fcr.request_id, fcpt.user_concurrent_program_name, fcr.request_date, fcr.actual_start_date, fu.user_name, fu.description, fcr.requested_start_date, fcr.argument_text FROM fnd_concurrent_requests fcr, fnd_concurrent_programs_tl fcpt, fnd_user fu WHERE phase_code IN ( 'R' ) AND status_code IN ( 'R' ) AND fcr.concurrent_program_id = fcpt.concurrent_program_id AND fcr.requested_by = fu.user_id -- And fcr.REQUEST_ID = '9675728' -- AND user_name != 'SYSADMIN' -- AND fcpt.USER_CONCURRENT_PROGRAM_NAME like 'Invoice Validation' ORDER BY 4;
--Query to see hidden parameters in oracle database:
SELECT X.KSPPINM NAME, DECODE(BITAND(KSPPIFLG / 256, 1), 1, 'TRUE', 'FALSE') SESMOD, DECODE( BITAND(KSPPIFLG / 65536, 3), 1, 'IMMEDIATE', 2, 'DEFERRED', 3, 'IMMEDIATE', 'FALSE' ) SYSMOD, KSPPDESC DESCRIPTION FROM SYS.X_ $ KSPPI X WHERE X.INST_ID = USERENV('INSTANCE') AND TRANSLATE(KSPPINM, '_', '#') LIKE '#%' AND upper(X.KSPPINM) like '%PGA%' ORDER BY 1 ;
-- sessions with highest CPU consumption
SELECT s.SID, s.serial #, p.spid AS "OS PID", s.username, s.module, st.VALUE / 100 AS "CPU sec" FROM v $ sesstat st, v $ statname sn, v $ session s, v $ process p WHERE sn.NAME = 'CPU used by this session' -- CPU AND st.statistic # = sn.statistic # AND st.SID = s.SID AND s.paddr = p.addr AND s.last_call_et < 1800 -- active within last 1/2 hour AND s.logon_time > (SYSDATE - 240 / 1440 ) -- sessions logged on within 4 hours ORDER BY st.VALUE desc;
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
*//
//*
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';
No comments:
Post a Comment