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 12.1.0.1. Please find Syed’s original note here:
https://www.linkedin.com/pulse/how-list-patches-details-through-sql-statement-syed-jaffar-hussain

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 “12.1.0.1 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

 
maciej tokar
Maciej Tokar

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

 
View Maciej Tokar's profile on LinkedIn         logoDB4
LinkedIn Auto Publish Powered By : XYZScripts.com