Syed Jaffar Hussain has published an interesting note on how to query database with SQL for patches that have been installed. It works starting from version 126.96.36.199. Please find Syed’s original note here:
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;
You can of course find the patches installed by traditional opatch lsinventory command or in the alert.log.
Please mind there has been a bug related to this feature “188.8.131.52 datapatch issue : ORA-27477: “SYS”.”LOAD_OPATCH_INVENTORY_1″ already exists (Doc ID 1934882.1)”
I have also encountered some problems with that query in the Oracle Restart environment with role separation.
Please take a look here : http://dba24.pl/2016/07/18/dbms_qopatch-problem-with-oracle-restart-and-role-separation/
About the author
An Oracle technology geek and crazy long distance runner, DBA24 Owner
Senior Oracle DBA / Consultant / [OCP10g, OCP12c, OCE RAC 10g] / [experience: 9y+]
Currently working for Bluegarden (Oslo Norway) by Miratech Group
Past: Mastercard / Trevica by Britenet, Citi International PLC, PZU