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就不是一回事了.
表结构:
在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关联:
由于merge过的patch name没有实际意义,所以我们一般通过ad_bugs来验证某个patch是否已经打过了.
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
一般情况下一个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_date
FROM ad_applied_patches aap
,ad_patch_drivers apd
,ad_comprising_patches acp
,ad_bugs ab
WHERE 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_date
FROM ad_bugs
WHERE 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
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