— Check Patches Applied or not :
- Oracle EBS R12.2.x :
select ad_patch.is_patch_applied ('R12',-1,&EnterPatchNumber) from dual;
Expected Results:
EXPLICIT = Patch is applied.
NOT APPLIED = Patch is not applied.- EBS 11i and R12.1 use below queries to check weather patch applied or not.
select * from ad_bugs where bug_number='bug_number';
select * from ad_applied_patches where patch_name='bug_number';— To Check Node (Single/Multi) :
select NODE_NAME,SUPPORT_CP,SUPPORT_FORMS,SUPPORT_WEB,SUPPORT_ADMIN from FND_NODES;— To Check Concurrent Program Details :
select * from fnd_concurrent_programs_tl where user_concurrent_program_name like 'Demo %'; -- Give your program name.— To Check EBS Version
select release_name from FND_PRODUCT_GROUPS;— Find Oracle Process ID By Concurrent Request ID :
select oracle_process_id from fnd_concurrent_requests where request_id='&v_request_id';— Query to find pending concurrent requests :
select count(*)
from APPS.FND_ 2 CONCURRENT_PROGRAMS_VL a,
APPS.FND_CONCURRENT_REQUESTS b
where a.CONCURRENT_PROGRAM_ID = b.CONCURRENT_PROGRAM_ID
and a.APPLICATION_ID = b.PROGRAM_APPLICATION_ID
and b.PHASE_CODE = 'P'
and b.requested_start_date <= sysdate;— Pending job details :
SELECT c.user_name, request_id, phase_code, status_code, hold_flag,
TO_CHAR(requested_start_date,'DD-MON-YY:HH24:MM:SS') Requested_Start_Date,
user_concurrent_program_name, b.concurrent_program_id
FROM applsys.fnd_concurrent_requests a,
applsys.fnd_concurrent_programs_tl b,
applsys.fnd_user c
WHERE a.phase_code = 'P'
AND a.concurrent_program_id = b.concurrent_program_id
AND b.LANGUAGE = 'US'
AND c.user_id = a.requested_by
ORDER BY user_concurrent_program_name;— Cancel scheduled concurrent Request “Gather Schema Statistics” :
update fnd_concurrent_requests
set phase_code='C',status_code='D'
WHERE phase_code = 'P'
AND status_code in ('Q','I') and concurrent_program_id=38121;commit;— Putting all concurrent jobs on hold:
update applsys.fnd_concurrent_requests set hold_flag='Y' where phase_code in ('R','P','I');— To check status of running requests:
select substr(fcrv.request_id,1,8)REQUEST,
decode(fcrv.phase_code,'P','Pending','R','Running','I','Inactive','Completed')PHASE,
decode(fcrv.status_code,
'A','Waiting',
'B','Resuming',
'C','Normal',
'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',fcrv.status_code)STATUS,
substr(fcrv.program,1,40)PROGRAM,substr(fcrv.PROGRAM_SHORT_NAME,1,15)SHORT,
substr(fcrv.requestor,1,15)REQUESTOR,
-- to_char(fcrv.actual_start_date,'MM/DD/RR HH24:MI')START_TIME,
round(((sysdate - fcrv.actual_start_date)*1440),1)RUN_TIME,
substr(fcr.oracle_process_id,1,7)OSPID,s.sid,s.serial#
from apps.fnd_conc_req_summary_v fcrv,
apps.fnd_concurrent_requests fcr,
v$session s,v$process p
where fcrv.phase_code = 'R'
and fcrv.request_id = fcr.request_id
and s.paddr = p.addr
and fcr.oracle_process_id = p.spid
and fcrv.concurrent_program_id not in ('40112','40113','36887')
--and trunc(fcrv.actual_start_date) like trunc(sysdate)
order by PHASE, STATUS, REQUEST desc;— To Check Menu Assigned to Which Responsibilities.
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 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 user_menu_name ='INV_NAVIGATE'
AND a.LANGUAGE = 'US';— To Check Responsibilities Assigned to Particular User
SELECT fu.user_id, fu.user_name, fur.responsibility_id,fr.responsibility_name
FROM apps.fnd_user fu, apps.fnd_user_resp_groups fur, apps.fnd_responsibility_vl fr
WHERE fu.user_id = fur.user_id
AND fr.application_id = fur.responsibility_application_id
AND fr.responsibility_id = fur.responsibility_id
AND TRUNC (SYSDATE) BETWEEN TRUNC (fr.start_date)
AND TRUNC (NVL ((fr.end_date - 1), SYSDATE))
AND TRUNC (SYSDATE) BETWEEN TRUNC (fur.start_date)
AND TRUNC (NVL ((fur.end_date - 1), SYSDATE))
and user_name like 'GBORASANIYA' --- for all user or for perticular user
AND fur.responsibility_application_id = 275 -- to check users for perticular responsibility
order by user_name;— Query to find all APPLICATION (module) information
SELECT fa.application_id "Application ID",
fat.application_name "Application Name",
fa.application_short_name "Application Short Name",
fa.basepath "Basepath"
FROM fnd_application fa,fnd_application_tl fat
WHERE fa.application_id = fat.application_id
AND fat.language = USERENV('LANG')
AND fat.application_name = 'Payables' -- <change it>
ORDER BY fat.application_name;— Finding Trace File of a Concurrent Program
SELECT 'Request id: '||request_id,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name: '||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'Prog. Name: '||prog.user_concurrent_program_name,
'File Name: '||execname.execution_file_name|| execname.subroutine_name,
'Status : '||decode(phase_code,'R','Running')
||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#,
'Module : '||ses.module
from fnd_concurrent_requests req,v$session ses,v$process proc,v$parameter dest,v$parameter dbnm,fnd_concurrent_programs_vl prog,fnd_executables execname
where req.request_id = &request
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id;— Finding SID of a Concurrent Request
select b.sid, oracle_session_id, oracle_process_id, os_process_id,status,client_identifier
from apps.fnd_concurrent_requests a,gv$session b
where a.request_id=&request_id
and a.ORACLE_SESSION_ID = b.AUDSID;— Query to get incompatible programs
SELECT fat.APPLICATION_NAME,fctl.user_concurrent_program_name,DECODE (TO_RUN_TYPE, 'S', 'Set', 'Program') TYPE,DECODE (INCOMPATIBILITY_TYPE, 'G', 'Global', 'Domain') "Incompatibilty Type"
FROM FND_CONCURRENT_PROGRAM_SERIAL fcrs,FND_CONCURRENT_PROGRAMS_TL fctl,FND_APPLICATION_TL fat
WHERE fcrs.RUNNING_APPLICATION_ID = fat.application_id
AND (RUNNING_CONCURRENT_PROGRAM_ID = 32045) -- CP id of the program in question
AND fctl.CONCURRENT_PROGRAM_ID = fcrs.TO_RUN_CONCURRENT_PROGRAM_ID
AND fctl.LANGUAGE = 'US'
AND fat.LANGUAGE = 'US'
ORDER BY to_run_application_id, to_run_concurrent_program_id;— Active Connected Users Connected to OACORE
select icx.node_id,fnd.node_name, count( distinct icx.session_id) How_many_user_sessions from icx_sessions icx, fnd_nodes fnd where icx.disabled_flag<>'Y'
and icx.PSEUDO_FLAG = 'N'
and icx.node_id=fnd.node_id
and (icx.last_connect + decode(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'), NULL,icx.limit_time, 0, icx.limit_time,
FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) > sysdate and counter < icx.limit_connects
group by icx.node_id,fnd.node_name;— Query To Get Concurrent Request ID for a Given Oracle SID
SELECT s.inst_id,a.request_id,s.sid,s.serial#,c.spid
FROM apps.fnd_concurrent_requests a, gv$process c, gv$session s
WHERE s.sid in ('&SID')
AND s.paddr = c.addr
AND a.oracle_process_id = c.spid
AND a.phase_code = UPPER ('R');— Get History of concurrent program
select request_id,user_concurrent_program_name,to_char(actual_start_date,'DD/MON HH24:MI:SS') START_TIME,to_char(ACTUAL_COMPLETION_DATE,'DD/MON HH24:MI:SS') END_TIME, (actual_completion_date-actual_start_date)*24*60 comp_time, argument_text,user_name, status_code, phase_code
from apps.fnd_concurrent_requests, apps.fnd_concurrent_programs_tl,apps.fnd_user
where fnd_concurrent_requests.concurrent_program_id = fnd_concurrent_programs_tl.concurrent_program_id
and user_concurrent_program_name ='XXVEMEA: SO99 Import Interface'
and fnd_concurrent_programs_tl.language='US'
and requested_by=user_id
and actual_start_date >(sysdate-1)
order by actual_start_date desc,ACTUAL_COMPLETION_DATE desc;— Get Alert name for specific recipient :
SELECT alert_id,alert_name
FROM APPS.alr_alerts
WHERE alert_id in (
select alert_id from apps.ALR_ACTIONS_V where upper(TO_RECIPIENTS) like '%RHASSIJA%' or upper(CC_RECIPIENTS) like '%ABCD%' or upper(BCC_RECIPIENTS) like '%ABCD%' ); -- Replace ABCD with the username.— Concurrent Request with parameters :
SELECT unique user_concurrent_program_name,responsibility_name,request_date,argument_text,request_id,phase_code,status_code,user_name
FROM apps.fnd_concurrent_requests fcr,apps.fnd_concurrent_programs_tl fcp,apps.fnd_responsibility_tl fr,apps.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 request_id in ('99326101','99327431','99326743','99328084','99327436','99328135')
ORDER BY REQUEST_DATE DESC;— Concurrent Request from OS Process ID :
SELECT user_concurrent_program_name,responsibility_name,request_date,argument_text,request_id,phase_code,status_code,logfile_name,outfile_name,output_file_type
FROM apps.fnd_concurrent_requests fcr, apps.fnd_concurrent_programs_tl fcp, apps.fnd_responsibility_tl fr, apps.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 request_id in (SELECT a.request_id FROM apps.fnd_concurrent_requests a, gv$process c, gv$session s WHERE s.sid in
(SELECT sid FROM v$session a,v$process b
WHERE a.paddr = b.addr
AND spid in ('93146','98197','85311','94391'))
AND s.paddr = c.addr
AND a.oracle_process_id = c.spid
AND a.phase_code = UPPER ('R'))
ORDER BY REQUEST_DATE DESC;— Current objects accessed by SID :
SELECT a.object,a.type,a.sid,b.username,b.osuser,b.program
FROM gv$access a,gv$session b
WHERE a.sid = b.sid
AND a.sid = &enter_session_id
ORDER BY a.object;— Check current frmweb session :
col CLIENT_IDENTIFIER format a10
col MODULE format a25
col MACHINE format a10
select sid, serial#, logon_time, client_identifier, module, status, machine, seconds_in_wait
from gv$session
where program like 'frmweb%'
order by logon_time;— Failed Login Attempts :
- Query 1 :
SELECT B.USER_NAME, U.ATTEMPT_TIME
FROM applsys.FND_UNSUCCESSFUL_LOGINS U, applsys.FND_USER B
WHERE U.USER_ID = B.USER_ID
AND TRUNC(U.ATTEMPT_TIME) > TRUNC(SYSDATE-30)
ORDER BY U.ATTEMPT_TIME;- Query 2 :
set verify off
set pages 200
set linesize 1000
column sid form 999
column spid for a5 heading "Unix|Serv|Pid" trunc
column logon_time format a13
column user_name form a12 heading "Apps User"
column description form a25 heading "Description" trun
column action form a30 heading "Responsibility Name" trun
column duration heading "Duraion (M)" format 9999.99
column idle form 9999.99
column module Heading "Module " format a13 trunc
select s.SID, p.SPID, to_char(logon_time,'dd-mon hh24:mi') logon_time,round(((sysdate - logon_time)*24*60),2) "Duration",last_call_et/60 "Idle",f.user_name, u.description, s.module,s.action
from v$session s, v$process p, APPS.FND_SIGNON_AUDIT_VIEW f, APPS.FND_USER u
where p.addr = s.paddr
and s.username is not null
and f.pid = p.pid
and f.process_spid = p.spid
and f.user_id = u.user_id
order by f.user_name;— Check HTTP/HTTPS Status :
- Query 1 :
select decode(UPPER(SUBSTR(APPS.FND_WEB_CONFIG.PROTOCOL,1,5)), 'HTTPS','HTTPS_ENABLED', 'HTTPS_DISABLED') "SSL Mode" from dual;- Query 2 :
select decode(UPPER(SUBSTR(FND_WEB_CONFIG.PROTOCOL,1,5)),'HTTPS','SSL/TLS is enabled','WARNING: SSL/TLS is not enabled') "SSL Mode" from dual;— Check if module is installed :
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
and patch_level like '%ATG%';— Check AD-TXK Level :
SELECT ABBREVIATION,NAME,codelevel FROM apps.AD_TRACKABLE_ENTITIES WHERE abbreviation in ('txk');— Tests if the default Guest user credentials :
select fnd_web_sec.validate_login('GUEST','ORACLE') from dual;You may also verify other Front-End user and their password as well.
select fnd_web_sec.validate_login('SYSADMIN','PASSWORD') from dual;Disclaimer:
This is an independent personal blog and is not affiliated with, sponsored by, or endorsed by Oracle Corporation. The views, explanations, and experiences expressed here are the author’s own. Commands, scripts, and procedures may be based on Oracle’s official documentation and are provided for educational and reference purposes only. Always test and validate them before use in a Live/Production environment. Sample output shown in this article was generated during the author’s own testing and may vary depending on the environment.
Please read full Disclaimer.