how to find the Oracle database version

Many times questions are being asked about how to find the Oracle database version and version of various Oracle utilities in Linux, Solaris, AIX, and Windows. Let’s check out how is the oracle database version defined and How to find the Oracle database version

Table of Contents

Oracle Database Versions

Oracle has released various versions of the Oracle database starting in 1979. They released Oracle 8i in 1998 where “i” stands for the internet.
They released Oracle 10g in 2003 where “g” stands for grid.

Then they released Oracle 12c in 2013 where “c” stands for the cloud.
After that, they continued year-wise starting in 2018. So 2018 was 18c Release, 2019 was 19c Release, and so on.
The latest version right now is 23c released in 2023. They are terming some versions as innovation releases and some as Long-term releases.

Innovation releases have shorter support periods ie. 2 years and long-term releases have 7-8 year support periods. So businesses can choose the release accordingly.
Right Now, 19c is the long-term release, and 18c,20c, and 21c are innovation releases. The next long-term release will 23c

How to check Oracle database version in Linux or any OS/how to check Oracle version in Linux

We can easily check the version using any of these six methods. You need to source the environment and then they can use any of these methods

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Prod
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for 32-bit Windows: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

or
you could use opatch also

$ORACLE_HOME/OPatch/opatch lsinventory

select version from v$instance;
or
Starting with 18c
select version,VERSION_FULL from v$instance;

you can query PRODUCT_COMPONENT_VERSION

SELECT
VERSION
FROM PRODUCT_COMPONENT_VERSION;
or
Starting with 18c
SELECT
VERSION,
VERSION_FULL
FROM PRODUCT_COMPONENT_VERSION;

You can query dba_registry

SELECT version FROM dba_registry WHERE comp_id = 'CATALOG'
Or
Starting with 18c
SELECT version,version_full FROM dba_registry WHERE comp_id = 'CATALOG'

Using PLSQL Package DBMS_DB_VERSION

set serveroutput on 
BEGIN
DBMS_OUTPUT.PUT_LINE('Oracle version is ' || dbms_db_version.version || '.' || dbms_db_version.release);
END;

How to find Client sqlplus version in use

sqlplus -v
SQL*Plus: Release 10.1.0.5.0 - Production

How to find the version of expdp

expdp
Export: Release 11.2.0.4.0 - Production on Saturday, 11 October, 2015 0:22:44

How to find the version of the nid utility

$nid
DBNEWID: Release 11.2.0.4.0 - Production on Saturday, 11 October, 2015 0:22:44

How to find the version of exp

$exp
Export: Release 11.2.0.4.0 - Production on Saturday, 11 October, 2015 0:22:44

How to find the version of rman

$rman

How to check Oracle version in sql developer

In SQL Developer, click the Reports tab on the left, near the Connections navigator. (If this tab is not visible, click View, then Reports.)

In the Reports navigator, expand Data Dictionary Reports.
Under Data Dictionary Reports, expand About Your Database.
Under About Your Database, click Version Banner.

how to check oracle database version in sql developer

How to check oracle client version in Linux

We can simply do these to find it

sqlplus -v 
SQL*Plus: Release 10.1.0.5.0 - Production

tnsping

Some Explanation about Version

Major Database Release Number	The first digit is the most general identifier. It represents a major new version of the software that contains significant new functionality.
Database Maintenance Release Number	The second digit represents a maintenance release level. Some new features may also be included.
Application Server Release Number	The third digit reflects the release level of the Oracle Application Server (OracleAS).
Component-Specific Release Number	The fourth digit identifies a release level specific to a component. Different components can have different numbers in this position depending upon, for example, component patch sets or interim releases.
Platform-Specific Release Number	The fifth digit identifies a platform-specific release. Usually, this is a patch set. When different platforms require the equivalent patch set, this digit will be the same across the affected platforms.

Also reads
Oracle database cloning: Check this page for Oracle database cloning which is an important role of an Oracle DBA. See steps by steps instructions with an explanation
DBMS_REDEFINITION : Online table redefinition allows for modification of the table structure definition without significantly affecting the availability of the table.
CPU vs Core Vs Socket: Find out what is CPU vs Core Vs Socket, difference and how to find them in windows using the different commands in an easy manner,
clone database from cold backup: This post consists of step by step detailed instructions to clone the oracle database using the cold backup database cloning method
dbms_metadata.get_ddl materialized view: Check out how to get table definition in oracle, oracle show index definition, get ddl of a materialized view in oracle, get the query of a view in oracle
opatch version command : check out this post on How to check the opatch version or opatch version command, apply the patch(opatch apply), Upgrade, opatch lsinventory
DBMS_DB_VERSION