PUBLIC privileges: reading the DB Version

It should come as no surprise DBAs or other users with similarly elevated privileges have access to many objects that can give them visibility into the nature of the database, including its version. V$INSTANCE and DBA_REGISTRY are commonly used views to see not only the version number; but other useful information as well.

But, what if an application is running with restricted access and needs to know the version? This is still solvable and in a variety of ways.

Perhaps the most obvious way is to read the v$version view which has PUBLIC SELECT grants by default.

SQL> select banner from v$version;

BANNER
---------------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

If for some reason that view is inaccessible, you still have options. While you may not have access to DBA_REGISTRY, you should still be able to query ALL_REGISTRY_BANNERS which has PUBLIC SELECT by default. Assuming you’re not in middle of upgrading or patching, the Catalog Views version should match that of the database instance.

SQL> select banner from all_registry_banners;

BANNER
-------------------------------------------------------------------
Oracle Database Catalog Views Release 21.0.0.0.0 - Production
Oracle Database Packages and Types Release 21.0.0.0.0 - Production
Spatial Release 21.0.0.0.0 - Production
Oracle Label Security Release 21.0.0.0.0 - Production
Oracle Text Release 21.0.0.0.0 - Production
Oracle Locator Release 21.0.0.0.0 - Production
Oracle Database Vault Release 21.0.0.0.0 - Production
Oracle Application Express Release 20.2.0.00.20 - Production

With a regular expression we can extract just the version information from the catalog row.

SQL> SELECT REGEXP_SUBSTR(banner, '[0-9]+(.[0-9]+)+')
   2    FROM all_registry_banners
   3   WHERE banner LIKE '%Catalog Views%';

 REGEXP_SUBSTR(BANNER,'[0-9]+(.[0-9]+)+')
-------------------------------------------
 21.0.0.0.0

PL/SQL can also tell you the version, but not in the same way. Using the DBMS_DB_VERSION package (with PUBLIC EXECUTE) you can get the version number and the major release number, but not any of the lower release digits.

SQL> BEGIN
   2      DBMS_OUTPUT.put_line(DBMS_DB_VERSION.version);
   3  END;
   4  /
 21

 PL/SQL procedure successfully completed.

 SQL> BEGIN
   2      DBMS_OUTPUT.put_line(DBMS_DB_VERSION.version || '.' || DBMS_DB_VERSION.release);
   3  END;
   4  /
 21.0

And, in 12cR2 or higher you can expose these constants from within SQL with an embedded function via the WITH-clause.

SQL> WITH
   2      FUNCTION get_version
   3          RETURN varchar2
   4      AS
   5      BEGIN
   6          RETURN DBMS_DB_VERSION.version || '.' || DBMS_DB_VERSION.release;
   7      END;
   8  SELECT get_version
   9    FROM DUAL;
  10  /

GET_VERSION
--------------
21.0

Thanks to Iudith Mentzel who reminded me of another option I forgot to include in my original posting. The DBMS_UTILITY package also has public grants by default. Like the DBMS_DB_VERSION, you can use it directly in PL/SQL, or invoke it through an embedded function via the WITH-clause, both forms are shown below.

SQL> DECLARE
   2      v_version         VARCHAR2(50);
   3      v_compatibility   VARCHAR2(50);
   4  BEGIN
   5      DBMS_UTILITY.db_version(v_version, v_compatibility);
   6      DBMS_OUTPUT.put_line(v_version);
   7  END;
   8  /

21.0.0.0.0

PL/SQL procedure successfully completed.

SQL> WITH
   2      FUNCTION get_version
   3          RETURN VARCHAR2
   4      AS
   5          v_version         VARCHAR2(50);
   6          v_compatibility   VARCHAR2(50);
   7      BEGIN
   8          DBMS_UTILITY.db_version(v_version, v_compatibility);
   9          RETURN v_version;
  10      END;
  11  SELECT get_version
  12    FROM DUAL;
  13  /

GET_VERSION
-------------
21.0.0.0.0

Hopefully you won’t need to jump through excessive hoops if you or your application needs to do a version lookup. The point here though was simply to show there are options even with a minimally privileged account. Questions and comments, as always, are welcome. If anyone knows of any additional documented and public methods I would be more than happy to hear about them and add to the list above.