Tuesday, December 27, 2016

How to list the DB patch details through a SQL statement

I'm interesting SQL statement which can be used (>=12c) to query the list of patches that applied on the database.
Apartment from the traditional lsinventory' to list the DB patching information, you can use one of the following methods to list the same information:
View alert.log (>=12.1.0.2)
Use the below query

with a as (select dbms_qopatch.get_opatch_lsinventory patch_output from dual)
select x.*
from a,
xmltable('InventoryInstance/patches/*'
passing a.patch_output
columns
patch_id number path 'patchID',
patch_uid number path 'uniquePatchID',
description varchar2(80) path 'patchDescription',
applied_date varchar2(30) path 'appliedDate',
sql_patch varchar2(8) path 'sqlPatch',
rollbackable varchar2(8) path 'rollbackable'
) x;
Apparently there is a bug wen the query is executed on 12.1.0.1. Read "12.1.0.1 datapatch issue : ORA-27477: "SYS"."LOAD_OPATCH_INVENTORY_1" already exists (Doc ID 1934882.1)"