Patch Impact Analysis

Hi,

As DBA, we are frequently asked to apply patch in the test environment. Sometimes the requestor may ask the file version which is very easy for a DBA.

But when the requested ask us to find the object impacted by the patch needs some attention. There may not be lot of links available in the net. Easy way to find the object impacted by a patch is to issue the query which will ask for patch number. Voila!!! you get the objects...

Patch Impact Analysis (or) Object Impact Analysis:-

set lines 300
column site_version format a30
column patch_version format a30
select f.bug_number "BUG_NBR",
a.filename "FILE_NAME",
substr(c.version,1,20) "PATCH_VERSION",
substr(d.version,1,20) "SITE_VERSION",
b.executed_flag,
b.creation_date "PATCH_DATE"
from
ad_files a,
ad_patch_run_bug_actions b,
ad_file_versions c,
ad_file_versions d,
ad_patch_run_bugs e,
ad_bugs f
where a.file_id=b.file_id
and c.file_version_id=b.patch_file_version_id
and d.file_version_id=b.onsite_file_version_id
and b.executed_flag = 'Y'
and e.bug_id=f.bug_id
and e.patch_run_bug_id=b.patch_run_bug_id
and f.bug_number='&Patch_number'
order by b.creation_date desc;

Sometimes, the requester may ask us which patch has affected a particular file. we can use the following query to find the answer:-

set lines 300
column site_version format a30
column patch_version format a30
select f.bug_number "BUG_NBR",
a.filename "FILE_NAME",
substr(c.version,1,20) "PATCH_VERSION",
substr(d.version,1,20) "SITE_VERSION",
b.executed_flag,
b.creation_date "PATCH_DATE"
from
ad_files a,
ad_patch_run_bug_actions b,
ad_file_versions c,
ad_file_versions d,
ad_patch_run_bugs e,
ad_bugs f
where a.file_id=b.file_id
and a.filename like '&FILE_NAME' -- use %
and c.file_version_id=b.patch_file_version_id
and d.file_version_id=b.onsite_file_version_id
and b.executed_flag = 'Y'
and e.bug_id=f.bug_id
and e.patch_run_bug_id=b.patch_run_bug_id
order by b.creation_date desc;

I hope this will be useful to many Apps DBAs

Thanks & Regards
Punith