Oracle DBA Querys

1. How to check if a patch is applied?
select * from ad_bugs
where bug_number = :bug_number;

select * from ad_applied_patches
where patch_name = :bug_number;

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 ':bug_number'
ORDER BY 1 DESC ;

2. 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 '%appl_short_name%';

3. 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));

5. How to find the latest version of a file on a given instance

SELECT   sub.filename
       , sub.VERSION
    FROM (SELECT adf.filename filename
               , afv.VERSION VERSION
               , RANK () OVER (PARTITION BY adf.filename ORDER BY afv.version_segment1 DESC
                , afv.version_segment2 DESC
                , afv.version_segment3 DESC
                , afv.version_segment4 DESC
                , afv.version_segment5 DESC
                , afv.version_segment6 DESC
                , afv.version_segment7 DESC
                , afv.version_segment8 DESC
                , afv.version_segment9 DESC
                , afv.version_segment10 DESC
                , afv.translation_level DESC) AS rank1
            FROM ad_file_versions afv
               , (SELECT filename
                       , app_short_name
                       , subdir
                       , file_id
                    FROM ad_files
                   WHERE UPPER (filename) LIKE UPPER ('%&filename%')) adf
           WHERE adf.file_id = afv.file_id) sub
   WHERE rank1 = 1
ORDER BY 1

You can enter partial file names and the search is not case sensitive.


For example you can search on "glxjeent" for the form "GLXJEENT.fmb" or "frmsheet1" for java file "FrmSheet1VBA.class".
Note: This script works for the following file types:
- .class, .drvx, .fmb, .htm, .lct, .ldt, .o, .odf, .pkb, .pkh, .pls, .rdf, .rtf, .sql, .xml.
It doens't work for .lpc, .lc files, etc. 

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


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

Note: the command adident (in unix, windows and other OS) can also be used to provide the file versions.