Check all patches details with DBMS_QOPATCH

Oracle 12c introduce DBMS_QOPATCH package which will give details of Oracle Patches applied on the database. Permission required: EXECUTE granted to the DATAPATCH_ROLE role. Location in RDBMS folder: rdbms/admin/dbmsqopi.sql

Check the already applied or rollback patches

SQL> set serverout on

SQL> exec dbms_qopatch.get_sqlpatch_status;

Check the count of Patches installed

SELECT dbms_qopatch.get_opatch_count FROM dual;

Verify the patch is applied ?

select xmltransform(dbms_qopatch.is_patch_installed('21359755'), dbms_qopatch.get_opatch_xslt) "Patch installed?" from dual;

Check all patches detail applied on database

-- Get list of patches
SELECT dbms_qopatch.get_opatch_list FROM dual;

-- Get patches as lsinventory -details command
select xmltransform(dbms_qopatch.get_opatch_lsinventory, dbms_qopatch.get_opatch_xslt) from dual;

Check the Oracle Home and inventory location for patches

select xmltransform(dbms_qopatch.get_opatch_install_info, dbms_qopatch.get_opatch_xslt) "Home and Inventory" from dual;