Queries Arsenel for Oracle Apps DBA - Part 1
**********************************************************
PRODUCT/CODE VERSION
**********************************************************
How to find versions of files in packages?
select text from dba_source
where name like ‘%&PKG_NAME%’
and line = 2;
Example:
select text
from dba_source
where name = ‘GLRX_JOURNAL_PKG’
and line = 2;
How to find out Package Header?
select name,text from dba_source where text like ‘%Header: %’
and owner = ‘APPS’ and name = ‘INVALID_OBJECT_NAME’;
How to find the latest version of a file on a given instance?
SELECT fi.file_id, filename, version
FROM apps.ad_files fi, apps.ad_file_versions ve
WHERE filename LIKE ‘&file_name’ AND ve.file_id = fi.file_id AND version =
(SELECT MAX (version) FROM apps.ad_file_versions ven WHERE ven.file_id =
fi.file_id);
Example 1 – finding java class version of a file).
SELECT fi.file_id, filename, version
FROM apps.ad_files fi, apps.ad_file_versions ve
WHERE filename LIKE ‘%FrmSheet1VBA%’ AND ve.file_id = fi.file_id AND version =
(SELECT MAX (version) FROM apps.ad_file_versions ven WHERE ven.file_id = fi.file_id);
Example 2 – Finding version of .fmb file.
SELECT fi.file_id, filename, version
FROM apps.ad_files fi, apps.ad_file_versions ve
WHERE filename LIKE ‘%GLXJEENT.fmb%’ AND ve.file_id = fi.file_id AND version =
(SELECT MAX (version) FROM apps.ad_file_versions ven WHERE ven.file_id = fi.file_id);
Note: This works for .class, .fmb .pls, .o, and .sql files only. Doens’t work
for .lpc,.lc files etc. If querying for a Form, please use GLXJEENT.fmb
as glxjeent.fmb will not work.
How to get the header file versions for an executable in Unix?
Example 1
Log into UNIX.
cd $AP_TOP/bin
strings -a APXXTR
grep Header
Example 2
cd $RG_TOP/bin
Strings -a RGRARG
grep Header
The above will provide the versions of all the header files in those executables.
**********************************************************
PATCHING
**********************************************************
How to check if a patch is applied?
select * from ad_bugs where bug_number = &bug_number;
or
select * from ad_applied_patches where patch_name = &bug_number;
or
SELECT DISTINCT a.bug_number,e.patch_name,
c.end_date,b.applied_flag
FROM ad_bugs a,
ad_patch_run_bugs b,
ad_patch_runs c,
ad_patch_drivers d,
ad_applied_patches e
WHERE a.bug_id = b.bug_id
AND b.patch_run_id = c.patch_run_id
AND c.patch_driver_id = d.patch_driver_id
AND d.applied_patch_id = e.applied_patch_id
AND a.bug_number LIKE ’123456′
ORDER BY 1 DESC ;
Patch Diffrence between two instances.
--Requirement is to create a DB_LINK from source to target.
--patches in EBUSL(Source) but not in EBUSD(Target)
(select distinct(ap.patch_name)
from ad_appl_tops at, ad_patch_driver_langs l, ad_patch_runs pr, ad_patch_drivers pd, ad_applied_patches ap
where pr.appl_top_id = at.appl_top_id
AND at.APPLICATIONS_SYSTEM_NAME = 'EBUSL'
and pr.patch_driver_id = pd.patch_driver_id
and pd.applied_patch_id = ap.applied_patch_id
and pd.patch_driver_id = l.patch_driver_id
--AND pr.start_date >= '15-APR-2009'
--AND pr.end_date <= '01-JUN-2009'
)
minus
(select distinct(ap.patch_name)
from ad_appl_tops@EBUSD at, ad_patch_driver_langs@EBUSD l, ad_patch_runs@EBUSD pr, ad_patch_drivers@EBUSD pd, ad_applied_patches@EBUSD ap
where pr.appl_top_id = at.appl_top_id
AND at.APPLICATIONS_SYSTEM_NAME = 'EBUSD'
and pr.patch_driver_id = pd.patch_driver_id
and pd.applied_patch_id = ap.applied_patch_id
and pd.patch_driver_id = l.patch_driver_id
--AND pr.start_date >= '15-APR-2009'
--AND pr.end_date <= '01-JUN-2009'
group by
ap.patch_name)
How to find the patch set level for an application?
select substr(aa.application_short_name,1,20) "Product", a.patch_level
"Patch Level" from fnd_product_installations a, fnd_application aa
where a.application_id = aa.application_id
and aa.application_short_name like ‘%&short_name%’;
Example:
select substr(aa.application_short_name,1,20) "Product", a.patch_level
"Patch Level" from fnd_product_installations a, fnd_application aa
where a.application_id = aa.application_id
and aa.application_short_name like ‘%AP%’;
How to find instance name, host name, apps and RDBMS versions of the instance user is logged into?
select i.instance_name, i.host_name,
f.release_name release, i.version
from v$instance i,
fnd_product_groups f where upper(substr(i.instance_name,1,4)) =
upper(substr(f.applications_system_name,1,4));
How to check the installation status and patch set level for a product?
Example 1
select patch_level, status from fnd_product_installations
where patch_level like ‘%FND%’;
Example 2
select patch_level, status from fnd_product_installations
where patch_level like ‘%XDO%’;
How to find the applications in the system that are either installed shared?
select fat.application_id, FAT.APPLICATION_NAME, fdi.status, fdi.patch_level FROM
FND_APPLICATION_TL FAT,
fnd_product_installations FDI
WHERE FDI.APPLICATION_ID = FAT.APPLICATION_ID
and fdi.status in (‘I’, ‘S’)
Note: Status ‘I’ meaning installed and status ‘S’ meaning shared.
How to check whether the product is install,shared and Not installed in Apps.
select t.application_name
, t.application_id
, i.patch_level
, decode(i.status,’I',’Fully Installed’,
‘N’,'Not Installed’,'S’,'Shared’,'Undetermined’) status
from fnd_product_installations i
, fnd_application_vl t
where i.application_id = t.application_id
order by t.application_id;
No comments:
Post a Comment