Retrieve Oracle's versions using a SQL query

3 min read | by Jordi Prats

One of the first things we might want to know about an Oracle database we have just connected is what version it is running. We can retrieve this information using an SQL query

There are several ways to get the version details. We can use the dynamic view v$version to get the data:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
BANNER_FULL
--------------------------------------------------------------------------------
BANNER_LEGACY
--------------------------------------------------------------------------------
    CON_ID
----------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
   0

BANNER
--------------------------------------------------------------------------------
BANNER_FULL
--------------------------------------------------------------------------------
BANNER_LEGACY
--------------------------------------------------------------------------------
    CON_ID
----------

Using product_component_version we can even be more specific:

SQL> select * from product_component_version;

PRODUCT
--------------------------------------------------------------------------------
VERSION
--------------------------------------------------------------------------------
VERSION_FULL
--------------------------------------------------------------------------------
STATUS
--------------------------------------------------------------------------------
Oracle Database 18c Enterprise Edition
18.0.0.0.0
18.3.0.0.0
Production

For example, we can use the following query to retrieve the product string:

SQL> select PRODUCT from product_component_version;

PRODUCT
--------------------------------------------------------------------------------
Oracle Database 18c Enterprise Edition

We can also use a SQL query to get the database versions ():

SQL> select VERSION from product_component_version;

VERSION
--------------------------------------------------------------------------------
18.0.0.0.0

Starting with Oracle database 18c, the column VERSION_FULL shows the version including the installed release update:

SQL> select VERSION_FULL from product_component_version;

VERSION_FULL
--------------------------------------------------------------------------------
18.3.0.0.0

If the database is in NOMOUNT state we can use the v_$instance view:

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

VERSION     VERSION_LEGACY    VERSION_FULL
----------------- ----------------- -----------------
18.0.0.0.0    18.0.0.0.0      18.3.0.0.0

SQL>

For older versions than Oracle 18c we can just select the VERSION column:

SQL> SELECT version FROM v$instance;

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

There are many ways of retrieving the current instance's Oracle version using a simple SQL query. But maybe the easiest way of getting the Oracle's database version is just by connecting using sqlplus. Once it connects to the database it will also show it's version:

[oracle@onepaydb /]$ sqlplus sys as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Jun 5 16:29:35 2021
Version 18.3.0.0.0

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

Enter password: 

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL>

Posted on 07/06/2021

Categories