Useful Apps DBA Queries - Install, Patch and Maintain Applications

How to determine the duration of applying a patch

SELECT AAP.PATCH_NAME "Patch Name",
       NVL(DECODE(MERGED_DRIVER_FLAG,
                  'Y',
                  AD_PA_VALIDATE_CRITERIASET.GET_CONCAT_MERGEPATCHES(APD.PATCH_DRIVER_ID),
                  ''),
           'Single Patch') "Merged Patches",
       APR.START_DATE "Start Date",
       APR.END_DATE "End Date",
       APD.DRIVER_FILE_NAME "Driver File",
       APR.PATCH_ACTION_OPTIONS "Patch Options",
       DECODE(APR.SERVER_TYPE_ADMIN_FLAG, 'Y', 'Admin,', NULL) ||
       DECODE(APR.SERVER_TYPE_FORMS_FLAG, 'Y', 'Forms,', NULL) ||
       DECODE(APR.SERVER_TYPE_NODE_FLAG, 'Y', 'Node,', NULL) ||
       DECODE(APR.SERVER_TYPE_WEB_FLAG, 'Y', 'Web,', NULL) "Server Type",
       APD.PLATFORM "Platform",
       SUBSTR(APR.PATCH_TOP, 1, 2000) "Patch Top",
       AD_CORE.GET_FORMATTED_ELAPSED_TIME((APR.END_DATE - APR.START_DATE),
                                          2) "Elapsed Time"
  FROM AD_PATCH_RUNS         APR,
       AD_PATCH_DRIVER_LANGS APDL,
       AD_PATCH_DRIVERS      APD,
       AD_APPLIED_PATCHES    AAP
 WHERE APD.PATCH_DRIVER_ID = APR.PATCH_DRIVER_ID
   AND APD.PATCH_DRIVER_ID = APDL.PATCH_DRIVER_ID
   AND APD.APPLIED_PATCH_ID = AAP.APPLIED_PATCH_ID
   AND AAP.PATCH_NAME = &PATCH_NAME
 ORDER BY END_DATE DESC;

How to determine affected objects by a patch

SELECT PROGRAM "Program",
       AAP.PATCH_NAME "Patch Name",
       APRT.JOB_NAME "Job Name",
       APRT.PHASE_NAME "Phase",
       APRT.START_TIME "Job Start Time",
       APRT.END_TIME "Job End Time",
       ROUND((NVL(APRT.END_TIME, SYSDATE) - APRT.START_TIME) * 60 * 24, 2) "Elapsed Time"
  FROM AD_PROGRAM_RUN_TASK_JOBS APRT,
       AD_PATCH_RUNS            APR,
       AD_PATCH_DRIVERS         APD,
       AD_APPLIED_PATCHES       AAP
 WHERE APRT.SESSION_ID = APR.SESSION_ID
   AND APR.PATCH_DRIVER_ID = APD.PATCH_DRIVER_ID
   AND APD.APPLIED_PATCH_ID = AAP.APPLIED_PATCH_ID
   AND AAP.PATCH_NAME = &PATCH_NAME
 ORDER BY "Elapsed Time" DESC