With Database 12c, Oracle has provided a handy feature, Queryable Patch Inventory, which allows for a SQL and PL/SQL interface to the product patch inventory. Previously, this information was only accessible through the OS command: OPatch. Now, with the DBMS_QOPATCH package, you can monitor distributed Opatch repositories from a central location. OPatch now supports inventory in XML format and inventory data is stored in database tables. So as data is stored in database tables, you can view installed database patches performing interactive queries using SQL*Plus. Oracle database 12c provides DBMS_QOPATCH package to manage this interface. It is installed by default during instance creation or by executing "dbmsqopi.sql" SQL script.

Few considerations

This package will work only if the database is OPEN (write mode).

Verify that the following database directories exist in database:

SQL> SELECT directory_name, directory_path
  2    FROM dba_directories
  3   WHERE directory_name like 'OPATCH%';

DIRECTORY_NAME                 DIRECTORY_PATH                                                       
------------------------------ -----------------------------------------------                   
OPATCH_LOG_DIR                 /u01/app/oracle/product/                        
OPATCH_SCRIPT_DIR              /u01/app/oracle/product/

Validate status for database objects related to Queryable Patch Inventory utility:

SQL> COL owner FORMAT a5
SQL> COL object_name FORMAT a25
SQL> COL object_type FORMAT a15
SQL> COL status FORMAT a6
SQL> SELECT owner, object_name, object_type, status
  2    FROM dba_objects
  3   WHERE object_name like '%OPATCH%';

OWNER OBJECT_NAME               OBJECT_TYPE     STATUS                                              
----- ------------------------- --------------- ------                                              
SYS   OPATCH_LOG_DIR            DIRECTORY       VALID                                               
SYS   OPATCH_SCRIPT_DIR         DIRECTORY       VALID                                               
SYS   OPATCH_XML_INV            TABLE           VALID                                               
SYS   OPATCH_XINV_TAB           TABLE           VALID                                               
SYS   OPATCH_INST_JOB           TABLE           VALID                                               
SYS   OPATCH_INST_PATCH         TABLE           VALID                                               
SYS   OPATCH_NODE_ARRAY         TYPE            VALID                                               
SYS   DBMS_QOPATCH              PACKAGE         VALID                                               
SYS   DBMS_QOPATCH              PACKAGE BODY    VALID                                               
SYS   LOAD_OPATCH_INVENTORY     JOB             VALID                                               

10 rows selected.

DBMS_QOPATCH execution output is returned in XML format. You will have to transform XML format into text output in order to make this output more "friendly". You can perform this task using GET_OPATCH_XSLT function as argument. This function returns the style-sheet for the opatch XML inventory presentation. GET_OPATH_XLST function is provided by Oracle as a component of DBMS_QOPATCH package. You can use this function as your default XSLT sheet or you can write your own XSLT sheet too.


You can start playing with the following functions to understand how this package works:


This function returns the ORACLE_HOME details, such as patch and inventory location:

SQL> SET pagesize 1000
SQL> SET long 50000
  2    xmltransform(dbms_qopatch.GET_OPATCH_INSTALL_INFO,
  3           dbms_qopatch.GET_OPATCH_XSLT)
  5    FROM dual;

ORACLE HOME DETAILS                                                                                 

Oracle Home       : /u01/app/oracle/product/                                           
Inventory         : /u01/app/oraInventory


This function lists all bugs applied in the Opatch repository:

SQL> SET long 50000
SQL> SET pagesize 10000
  2    xmltransform(dbms_qopatch.GET_OPATCH_BUGS, 
  3                  dbms_qopatch.GET_OPATCH_XSLT)
  4    as "BUGS FIXED"
  5    FROM dual;

BUGS FIXED                                                                                          

Bugs fixed:
17716305 17034172 16694728 16855202 17439871 16320173 17462687
7082983 16313881 16715647 17362796 17777061 16450169 16392068 17761775
6977973 14197853 16712618 17552800 17922172 17441661 16524071 16856570
7610418 17465741 17171530 16523150 16212405 16741246 16930325 16443657


This function provides a very useful list of patches installed, expanding the previous example not only including patch description and bugs fixed listing but also including files modified by each fix.

  2    xmltransform(dbms_qopatch.get_opatch_list(), 
  3                  dbms_qopatch.GET_OPATCH_XSLT())
  4    as "OPATCH LIST"
  5    FROM dual;

OPATCH LIST                                                                                         

Patch Details:

Patch(sqlpatch) 18031528: applied on 2014-04-17T16:04:49-03:00
Unique Patch ID: 17262469
Patch Description: Database Patch Set Update : (18031528)
Created on : 18 Mar 2014, 14:50:40 hrs PST8PDT
Bugs fixed:
17716305 17034172 16694728 16855202 17439871 16320173 17462687
7082983 16313881 16715647 17362796 17777061 16450169 16392068 17761775
7610418 17465741 17171530 16523150 16212405 16741246 16930325 16443657

Files Touched:



You can query if a specific patch ID is installed in your system as follows:

  2    xmltransform(dbms_qopatch.is_patch_installed(18031528),
  3                  dbms_qopatch.GET_OPATCH_XSLT())
  5    FROM dual;

PATCH INSTALLED                                                                                     

Patch Information:
18031528: applied on 2014-04-17T16:04:49-03:00

Writing your own XSLT sheet

You can write your own XLST sheet instead of using GET_OPATCH_XSLT() function.

Following the previous example, let's take a look at the IS_PATCH_INSTALLED function output:

SQL> SET linesize 60
SQL> SELECT dbms_qopatch.is_patch_installed(18031528)
  2    FROM dual;


We can create our new custom output providing a new XLST sheet as follows:

SQL> SELECT xmltransform(dbms_qopatch.is_patch_installed(18031528),
  2    '
  6     Patch Id= 
  7      Applied:  
  9     ') as "CUSTOM XLST SHEET"
 10    FROM dual;

CUSTOM XLST SHEET                                           
Patch Id= 18031528 Applied: true