How can one programmatically find the current Oracle patch set?

Question

In Identifying Your Oracle Database Software Release Oracle states that you can find your "platform-specific release number" (patch set) by querying PRODUCT_COMPONENT_VERSION:

To identify the release of Oracle Database that is currently installed and to see the release levels of other database components you are using, query the data dictionary view PRODUCT_COMPONENT_VERSION.

According to this we are using 11.2.0.3.0

SQL> select * from product_component_version;

PRODUCT                             VERSION         STATUS
----------------------------------- --------------- ---------------
NLSRTL                              11.2.0.3.0      Production
Oracle Database 11g                 11.2.0.3.0      64bit Production
PL/SQL                              11.2.0.3.0      Production
TNS for Linux:                      11.2.0.3.0      Production

The same occurs with V$VERSION (which PRODUCT_COMPONENT_VERSION is a view of incidentally):

SQL> select * from v$version;

BANNER
---------------------------------------------------------

Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

However, according to DBA_REGISTRY_HISTORY the database appears to be on 11.2.0.3.5¹:

SQL> select action, namespace, version, id, comments from dba_registry_history;

ACTION          NAMESPACE VERSION            ID COMMENTS
--------------- --------- ---------- ---------- ------------------------------
VIEW INVALIDATE                         8289601 view invalidation
UPGRADE         SERVER    11.2.0.3.0            Upgraded from 11.2.0.1.0
APPLY           SERVER    11.2.0.3            0 Patchset 11.2.0.2.0
APPLY           SERVER    11.2.0.3            5 PSU 11.2.0.3.5

DBA_REGISTRY_HISTORY doesn't necessarily have any data in it so I can't reliably use this view. And, Oracle doesn't seem to provide a standardised method of populating the comments field I seem to be left doing the following and then praying that it works.

select max(regexp_replace(comments, '[^[:digit:].]')) 
         keep (dense_rank first order by action_time desc)
  from dba_registry_history

Is there an easier, reliable, method of finding out the current version, including patch set, programmatically?

_{1. Also possible: I'm completely misreading this and people have forgotten what they patched.}

Answer 1

As I cannot guarantee that DBA_REGISTRY_HISTORY will be populated even though it seems to give the correct patch set I've ended up doing the following to populate from V$VERSION if there is nothing.

with drh as ( 
select max(regexp_replace(comments, '[^[:digit:].]')) 
            keep (dense_rank last order by action_time) as vers
  from dba_registry_history
       )
 , v$v as ( 
select regexp_substr(banner, '(\d+\.?){5}', 1) as vers
  from v$version 
 where lower(banner) like 'oracle%'
       )
select coalesce(drh.vers, v$v.vers) as patch_set
  from drh
 right outer join v$v
   on 1 = 1

This works because both queries will only return one row and I've tested it on 10.2, 11.2 and 12.1

It is, however, ridiculous and ugly. There's no guarantees that it won't break as everything's a free-text field and Oracle seems to change how it displays the data in these views occasionally. Also, Oracle isn't even consistent within these views. Here's a 12c database, notice the comments field magically reverts the patch set when being upgraded, and how the version and comments don't match.

SQL> select action, version, id, comments from dba_registry_history;

ACTION          VERSION          ID COMMENTS
--------------- ---------- -------- ------------------------
APPLY           11.2.0.3          0 Patchset 11.2.0.2.0
APPLY           11.2.0.3          0 Patchset 11.2.0.2.0
APPLY           11.2.0.3          5 PSU 11.2.0.3.5
VIEW INVALIDATE             8289601 view invalidation
UPGRADE         12.1.0.1.0          Upgraded from 11.2.0.3.0
APPLY           12.1.0.1          0 Patchset 12.1.0.0.0

6 rows selected.

So, it'd still be nice if this data was exposed in a manner that's easy to use.

Answer 2

-- list applied patches using sqlplus

SET linesize 200 pagesize 200
col action_time FOR a28
col version FOR a10
col comments FOR a35
col action FOR a25
col namespace FOR a12
SELECT * FROM registry$history;

-- list applied patches using opatch

$ORACLE_HOME/OPatch/opatch lsinventory

$ORACLE_HOME/OPatch/opatch lsinventory|grep "Patch description"

$ORACLE_HOME/OPatch/opatch lsinventory -details