Different ways to get the current database version

In this blog post I will show you how to get the current version of the connected database using either SQL queries or PL/SQL code.

To show you some enhancements in this area, I will use versions 12.1.0.2 and 19c for a comparison.

SQL

PRODUCT_COMPONENT_VERSION

Required privileges: none (SELECT privilege is granted to PUBLIC)

12.1.0.2

SQL> SELECT version FROM product_component_version 
      WHERE product LIKE 'Oracle Database%';

VERSION
-----------------
12.1.0.2.0

In older database versions this view returns more then one row. To get the required row the WHERE clause is mandatory.

19c

Starting with 18c, column VERSION_FULL shows the version including the installed release update.

SQL> SELECT version, version_full FROM product_component_version;

VERSION           VERSION_FULL
----------------- -----------------
19.0.0.0.0        19.5.0.0.0

V$INSTANCE

This view can be used while the database is in NOMOUNT state.

Required privileges: SELECT on SYS.V_$INSTANCE

SQL> GRANT SELECT ON sys.v_$instance TO scott;

12.1.0.2

SQL> SELECT version FROM v$instance;

VERSION
-----------------
12.1.0.2.0

19c

Starting with 18c, column VERSION_FULL shows the version including the installed release update.

SQL> SELECT version, version_legacy, version_full FROM v$instance;

VERSION           VERSION_LEGACY    VERSION_FULL
----------------- ----------------- -----------------
19.0.0.0.0        19.0.0.0.0        19.5.0.0.0

V$VERSION

This view provides the banner of the database and depending on the database version some additional information about core database components and can be used while the database is in NOMOUNT state.

To extract the version SUBSTR or REGEXP_SUBSTR have to be used.

Required privileges: none (SELECT privilege is granted to PUBLIC)

12.1.0.2

SQL> SELECT banner FROM v$version;

BANNER
------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for Solaris: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

19c

Starting with 18c, column BANNER_FULL was added and contains a multiline string which also includes the release update version number.

SQL> SELECT banner_full FROM v$version;

BANNER_FULL
------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

DBA_REGISTRY

For every installed database component DBA_REGISTRY provides its version number.

Required privileges: SELECT on SYS.DBA_REGISTRY

SQL> GRANT SELECT ON sys.dba_registry TO SCOTT;

12.1.0.2

SQL> SELECT version FROM dba_registry 
      WHERE comp_id = 'CATALOG'

VERSION
-----------------
12.1.0.2.0

19c

Starting with 18c, column VERSION_FULL shows the version including the installed release update.

SQL> SELECT version, version_full FROM dba_registry 
      WHERE comp_id = 'CATALOG';

VERSION           VERSION_FULL
----------------- -----------------
19.0.0.0.0        19.5.0.0.0

PL/SQL

DBMS_DB_VERSION

This package has no procedures or functions, but provides constant to get the version and release number. Constants can only be used within a PL/SQL block.

With this package it is not possible to get the patchset or release update version number.

12.1.0.2

SQL> SET SERVEROUTPUT ON
SQL> EXEC DBMS_OUTPUT.PUT_LINE(DBMS_DB_VERSION.VERSION || '.' || DBMS_DB_VERSION.RELEASE);

12.1

19c

SQL> SET SERVEROUTPUT ON
SQL> EXEC DBMS_OUTPUT.PUT_LINE(DBMS_DB_VERSION.VERSION || '.' || DBMS_DB_VERSION.RELEASE);

19.0

DBMS_UTILITY

Within a PL/SQL block procedure DB_VERSION can be used to extrace the current database version and the currently set compatbility version.

12.1.0.2

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
   v_version          VARCHAR2(50);
   v_compatibility    VARCHAR2(50);
BEGIN
   DBMS_UTILITY.DB_VERSION (
      version => v_version,
      compatibility => v_compatibility
   );
   
   DBMS_OUTPUT.PUT_LINE('Version:       ' || v_version);
   DBMS_OUTPUT.PUT_LINE('Compatibility: ' || v_compatibility);
END;
/

Version:       12.1.0.2.0
Compatibility: 12.1.0.2.0

19c

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
   v_version          VARCHAR2(50);
   v_compatibility    VARCHAR2(50);
BEGIN
   DBMS_UTILITY.DB_VERSION (
      version => v_version,
      compatibility => v_compatibility
   );
   
   DBMS_OUTPUT.PUT_LINE('Version:       ' || v_version);
   DBMS_OUTPUT.PUT_LINE('Compatibility: ' || v_compatibility);
END;
/

Version:       19.0.0.0.0
Compatibility: 19.0.0

Other

SQL*Plus

When SQL*Plus is used to connect to a database, it shows the banner of the connected database.

12.1.0.2

$> sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Feb 27 09:36:52 2020

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

SQL>

19c

$> sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 27 09:38:21 2020
Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL>