We often get the task to check patches applied in the oracle apps environment. Here are the Top queries to find Patch applied in EBS
How to find if the bug number or patch is applied to Apps EBS
select substr(APPLICATION_SHORT_NAME,1,10) Product,substr(BUG_NUMBER,1,10),Version,last_update_date applied_date from ad_bugs where BUG_NUMBER= to_char('&bug_no');
How to find the patch level of a particular application
select pi.patch_level, Application_short_name from
fnd_product_installations pi, fnd_application fa where fa.application_id
= pi.application_id and Application_short_name like '&App_Short_Name'
/
select patch_level from fnd_product_installations where patch_level LIKE ('%&1%');
How to find the EWT version
unzip -l $FND_TOP/java/jar/fndewt.jar | grep 3_
How to list out the various information of various product
set linesize 1000 column application_id format a999999 heading "APPL|ID" column application_short_name format a10 heading "APPL|SHORT NAME" column application_name format a50 heading "APPLICATION NAME" column language format a4 heading "LANG" select a.application_id, a.application_short_name, a.basepath, at.application_name from fnd_application a, fnd_application_tl at where at.application_id = a.application_id and at.language='US' group by a.application_id,a.application_short_name,a.basepath,at.application_name,at.language order by a.application_id /
How to find the installed status of all the products
set linesize 1000 column APPS format a10 select decode(nvl(a.APPLICATION_short_name,'Not Found'), 'SQLAP','AP','SQLGL','GL','OFA','FA', 'Not Found','id '||to_char(fpi.application_id), a.APPLICATION_short_name) apps, decode(fpi.status,'I','Installed','S','Shared', 'N','Inactive',fpi.status) status, fpi.product_version, nvl(fpi.patch_level,'-- Not Available --') Patchset, to_char(fpi.last_update_date,'dd-Mon-RRRR') "Update Date" from fnd_oracle_userid o, fnd_application a, fnd_product_installations fpi where fpi.application_id = a.application_id(+) and fpi.oracle_id = o.oracle_id(+) order by 1,2 /
This script helps in identifying the various versions of a particular file, the associated BUG NUMBERS, the Date on which it was applied.Input is the FILE NAME whose VERSION is to be determined
set linesize 1000
column filename format a15
column bug_number format a10
column version format a15
set pages 250
set verify off
select a.bug_number,h.filename,g.version,
to_char(c.start_date,'DD-MON-YYYY HH:MI:SS')start_time,
to_char(c.end_date,'DD-MON-YYYY HH:MI:SS')end_time,
to_char(to_date('00:00:00','HH24:MI:SS') +(c.end_date - c.start_date), 'HH24:MI:SS') Duration
from ad_bugs a,
ad_patch_run_bugs b,
ad_patch_runs c,
ad_patch_drivers d,
ad_applied_patches e,
ad_patch_run_bug_actions f,
ad_file_versions g,
ad_files h
where a.bug_id = b.bug_id
and b.patch_run_id = c.patch_run_id
and c.patch_driver_id = d.patch_driver_id
and d.applied_patch_id = e.applied_patch_id
and b.patch_run_bug_id = f.patch_run_bug_id
and f.patch_file_version_id = g.file_version_id
and g.file_id = h.file_id
and h.filename='&File_Name'
order by c.end_date desc
/
How to Finds the version information of any Oracle Database Package Information
select text from user_source where name='&package_name' and text like '%$Header%' /
You can tell if you’re on OAF 5.7 version OAF 5.10 on the database tier by running the following SQL:
select jdr_mds_internal.getRepositoryVersion from dual;
If it returns something like 9.0.3.6.0_### then you are on OAF 5.7. If it
returns 9.0.5.0.0_### then you are on OAF 5.10.
how to find .o file version
adident Header FNDCRM | grep afpcrm.oc
Related Articles
oracle apps queries for DBA: This page contains the very useful and practical Top 30 Useful oracle apps queries for APPS DBA to help in day-to-day administration activities
oracle dba interview questions and answers: check out awesome oracle dba interview questions and answers to succeed in any oracle database interviews. This will test your knowledge on various fronts
Concurrent Manager Queries :This page contains awesome top 30 Concurrent Manager Queries for Concurrent Manager troubleshooting ,resolution ,run time, details
40 Adpatch question every DBA should know : Every Apps DBA has to work with adpatch utility to performance patch maintenance.Here we provide most commonly asked adpatch questions
Patch wizard in Oracle EBS environment : How to use Patch wizard to analyze the patches in Oracle EBS