Saturday, June 24, 2017

Query to fetch PATCH action details














This is known query. But, still i would like to share the one which we use regularly for doing the impact analysis of Oracle Patch. 


Below query helps you to get the actions performed by a Patch application. 

Select J.PATCH_NAME,
       H.APPLICATIoNS_SYSTEM_NAME,
       H.NAME,
       I.DRIVER_FILE_NAME,
       D.APP_SHORT_NAME,
       D.SUBDIR,
       D.FILENAME,
       E.ACTION_CODE,
       max(F.VERSION) latest
  from AD_BUGS                  A,
       AD_PATCH_RUN_BUGS        B,
       AD_PATCH_RUN_BUG_ACTIONS C,
       AD_FILES                 D,
       AD_PATCH_COMMON_ACTIONS  E,
       AD_FILE_VERSIONS         F,
       AD_PATCH_RUNS            G,
       AD_APPL_TOPS             H,
       AD_PATCH_DRIVERS         I,
       AD_APPLIED_PATCHES       J
 where A.BUG_ID = B.BUG_ID
   and B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID
   and C.FILE_ID = D.FILE_ID
   and E.COMMON_ACtION_ID = C.COMMON_ACTION_ID
   and D.FILE_ID = F.FILE_ID
   and G.APPL_TOP_ID = H.APPL_TOP_ID
   and G.PATCH_DRIVER_ID = I.PATCH_DRIVER_ID
   and I.APPLIED_PATCH_ID = J.APPLIED_PATCH_ID
   and B.PATCH_RUN_ID = G.PATCH_RUN_ID
   and C.EXECUTED_FLAG = 'Y'
   and G.PATCH_DRIVER_ID in
       (select PATCH_DRIVER_ID
          from AD_PATCH_DRIVERS
         where APPLIED_PATCH_ID in
               (select APPLIED_PATCH_ID
                  from AD_APPLIED_PATCHES
                 where PATCH_NAME = '22136978'))
 GROUP BY J.PATCH_NAME,
          H.APPLICATIoNS_SYSTEM_NAME,
          H.NAME,
          I.DRIVER_FILE_NAME,
          D.APP_SHORT_NAME,
          D.SUBDIR,
          D.FILENAME,
          E.ACTION_CODE;

Hope it helps!!


Saturday, June 24, 2017 by Team search · 0

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.