[ORACLE EBS]2. Patch Information

查找apply the patch & mini patch:

SELECT   patch_name, patch_type
    FROM ad_applied_patches
ORDER BY creation_date 

-----------------------
2644657     ONE-OFF
2641718     ONE-OFF

SELECT   app_short_name, MAX (patch_level) AS patch_level
    FROM applsys.ad_patch_driver_minipks
GROUP BY app_short_name;

----------------------
AD     11i.AD.I.2
AR     11i.AR.O
AX     11i.AX.J

查找apply  patch的详细信息（这里查的是R12，可以看到application_short_name变成小写了）:

SELECT   b.patch_name, g.orig_bug_number, e.bug_number, f.NAME machine,
         a.patch_top, g.application_short_name app, g.applied_flag,
         MIN (a.start_date), MAX (a.end_date), b.patch_type,
         b.maint_pack_level
    FROM applsys.ad_patch_runs a,
         applsys.ad_applied_patches b,
         applsys.ad_patch_drivers c,
         applsys.ad_comprising_patches d,
         applsys.ad_bugs e,
         applsys.ad_appl_tops f,
         applsys.ad_patch_run_bugs g
   WHERE a.patch_driver_id = c.patch_driver_id
     AND b.applied_patch_id = c.applied_patch_id
     AND d.patch_driver_id(+) = a.patch_driver_id
     AND e.bug_id(+) = d.bug_id
     AND f.appl_top_id(+) = a.appl_top_id
     AND g.patch_run_id(+) = a.patch_run_id
     AND a.creation_date >=
                    TO_DATE ('2004-01-01', 'YYYY-MM-DD')
     AND g.applied_flag(+) = 'Y'
GROUP BY b.patch_name,
         b.patch_type,
         b.maint_pack_level,
         f.NAME,
         a.patch_top,
         e.bug_number,
         g.application_short_name,
         g.applied_flag,
         g.orig_bug_number
ORDER BY MIN (a.start_date)

 
----------------------------------------------------
4502962     4502962          TESTSERERER     /d1/R12/ab/apps/prodxb19comn/autobuild/patch/4502962     ad      Y     2007-1-2 13:58:45    2007-1-2 14:21:12    PATCH-SET           
4440000     4440000          TESTSERERER     /d1/R12/ab/apps/prodxb19comn/autobuild/patch/4440000     au      Y     2007-1-3 15:02:03    2007-1-4 14:21:09    MAINTENANCE-PACK     12.0.0
5436860     5436860          TESTSERERER     /d1/R12/ab/apps/prodxb19comn/autobuild/patch/5436860     fnd     Y     2007-1-4 21:45:38    2007-1-4 21:46:23    ONE-OFF             

得到PATCH LEVEL:

SELECT patch_level FROM fnd_product_installations

--------------------------------------------------
11i.QRM.A
11i.PON.H
11i.OKL.D
11i.IBC.A

如果想知道，具体的patch level,需要使用ORACLE 提供的工具Patchset Comparison Utility，
ftp://ftp.oracle.com/support/outgoing/PATCHSET_COMPARE_TOOL/patchsets.sh
不过这个东东跑起来一点也不快！

PATCH HISTORY DATABASE TABLES list:
AD_APPL_TOPS                                  Stores information about all APPL_TOPs utilizing this instance.
AD_APPLIED_PATCHES                   Stores information about all distinct updates applied.
如果有两个一样名字的PATCH，但是不同内容，会出现两笔记录。比如Merge patch 或者是在 多结点。
AD_BUGS                                             Stores information about all distinct bug fixes applied.
AD_DRIVERS                                       Stores information about the drivers that comprise an update.
DRIVER 的一些信息,有几个DRIVER，就有几条数据，比如cxxxxx,dxxxxx,gxxxxx或uxxxxx 。
AD_FILE_VERSIONS                         Stores version information for files.
AD_FILES                                             Stores information about files in the system.
AD_PATCH_COMMON_ACTIONS   Stores information about actions in update driver files.
AD_PATCH_DRIVER_LANGS          Stores information about the languages included in an update driver file.
AD_PATCH_DRIVER_MINIPKS       Stores information about mini-packs.
AD_PATCH_RUN_BUG_ACTIONS Stores detailed information about actions applied by an update.
AD_PATCH_RUN_BUGS                  Stores information about all bug fixes included in an update, applied or not.  记录当时APPLY 的信息，但是针对某个BUG。
AD_PATCH_RUNS                            Stores information about all invocations of AutoPatch for all of the various updates applied. 记录当时APPLY driver的信息，比如位置，开始，结束时间，同Driver个数对应。
AD_RELEASES                                  Stores release information about the Oracle Applications system.