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>