本文共 3580 字，大约阅读时间需要 11 分钟。

Oracle EBS R12 - ad_bugs.BUG_NUMBER和ad_applied_patches.PATCH_NAME的关系 一般情况下一个patch修复一个bug,因此patch_name和bug_number是相等的, 但是如果某个patch merge了多了patch,那么该patch就具有了修复多个bug的效果,此时的patch_name和bug_number就不是一回事了. 表结构:

SQL> desc ad_bugs; Name                                      Null?    Type ----------------------------------------- -------- ---------------------------- BUG_ID                                    NOT NULL NUMBER APPLICATION_SHORT_NAME                             VARCHAR2(50) BUG_NUMBER                                NOT NULL VARCHAR2(30) CREATION_DATE                             NOT NULL DATE ARU_RELEASE_NAME                          NOT NULL VARCHAR2(30) CREATED_BY                                NOT NULL NUMBER LAST_UPDATE_DATE                          NOT NULL DATE LAST_UPDATED_BY                           NOT NULL NUMBER TRACKABLE_ENTITY_ABBR                              VARCHAR2(8) BASELINE_NAME                                      VARCHAR2(150) GENERIC_PATCH                                      VARCHAR2(1)SQL> desc ad_applied_patches Name                                      Null?    Type ----------------------------------------- -------- ---------------------------- APPLIED_PATCH_ID                          NOT NULL NUMBER RAPID_INSTALLED_FLAG                               VARCHAR2(1) PATCH_NAME                                NOT NULL VARCHAR2(120) PATCH_TYPE                                NOT NULL VARCHAR2(30) MAINT_PACK_LEVEL                                   VARCHAR2(30) SOURCE_CODE                               NOT NULL VARCHAR2(3) CREATION_DATE                             NOT NULL DATE CREATED_BY                                NOT NULL NUMBER LAST_UPDATE_DATE                          NOT NULL DATE LAST_UPDATED_BY                           NOT NULL NUMBER IMPORTED_FLAG                                      VARCHAR2(1) IMPORTED_FROM_DB                                   VARCHAR2(30) IMPORTED_ID                                        NUMBER MERGE_DATE                                         DATE DATA_MODEL_DONE_FLAG                               VARCHAR2(1)

在patch没有被merge的情况下,ad_bugs.BUG_NUMBER和ad_applied_patches.PATCH_NAME的值是相等的. 在patch被merge过的情况下,需要通过一个中间表(ad_comprising_patches)来关联ad_bugs.BUG_NUMBER和ad_applied_patches.PATCH_NAME, 而且此时的patch name默认是merge时的driver file name(如driver file  name是u_financial_merged.drv,那么patch name就是financial_merged). 关联层次: ad_applied_patches -> ad_patch_drivers -> ad_comprising_patches -> ad_bugs 由于中间表ad_comprising_patches仅保存merged patch纪录,所以以下SQL只能查找到merged patch对应的patch name和bug number关联:

SELECT aap.patch_name      ,aap.patch_type      ,aap.last_update_date      ,apd.driver_file_name      ,apd.merged_driver_flag      ,apd.merge_date      ,ab.bug_number      ,ab.last_update_dateFROM   ad_applied_patches aap      ,ad_patch_drivers apd      ,ad_comprising_patches acp      ,ad_bugs abWHERE      aap.applied_patch_id = apd.applied_patch_id       AND apd.patch_driver_id = acp.patch_driver_id       AND acp.bug_id = ab.bug_id     --  AND apd.merged_driver_flag = 'Y'

由于merge过的patch name没有实际意义,所以我们一般通过ad_bugs来验证某个patch是否已经打过了.

SELECT bug_number      ,last_update_dateFROM   ad_bugsWHERE  bug_number = '8644899';

对于重复安转的patch可通过以下SQL来查询安装历史纪录：

select orig_patch_name,last_update_date from ad_patch_drivers where applied_patch_id in (select applied_patch_id from ad_applied_patches where patch_name='17086303');   -- patch re-install

REF: 1. Find the patches in a merged patch http://blog.trutek.com/ 2. PATCH related scripts http://raghuook.blogspot.com/2010/08/apps-dba-patch-related-scripts.html

转载地址：http://oitai.baihongyu.com/