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.51:
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.
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.