How to Find Your Oracle Database Version Number

When you connect to an Oracle database, you may need to know the exact version and edition you are connecting to, either to take advantage of fancy new features, or to limit yourself to legacy behavior. In this article we are going to review how to obtain that information.

In this article I’ll walk through:

• Oracle versions vs editions
• Finding the Oracle version while connected to Oracle
• Finding the Oracle version at connection time
• Finding the Oracle version in log files

Oracle Version vs Edition

In Oracle-land a version is a release of the database (eg 11.2). Each database version is available in several edition, like Express, or Standard.

First, let’s talk about Versions.

Like most software products, Oracle Database is constantly evolving, adding new features and fixing issues. In order to be able to identify with precision each product, Oracle uses a numbering convention that allows to identify them.

Oracle Versions until 2018

Oracle Database products until 2018 were identified with a sequence of 4 numbers. The first representing the major-version, which incremented on substantial changes. The second referenced the release, a release was created when new features were included but without a major rewriting of the software. The third and fourth numbers represented patch-sets, which were essentially bug fixes.

Examples

• Oracle version 11.2.0.1
• Oracle version 12.1.0.1

Versions since 2018

Since 2018, Oracle changed the numbering convention. The first number or version is the year in which the product is released. The second number represents the quarterly update, and the third represents the quarterly revision. You can check the Database Upgrade Guide to learn more about them.

Examples

• Oracle version 18.3.0
• Oracle version 19.3.0

(Lets hope they change this convention before the year 2118)

Oracle Version Examples

We can note that the three latest versions were all released in the third quarter of the year, so the initial version for all of them is YY.3.0.

Besides the numbering, Oracle uses some letters as a commercial “punch” to identify a version with specific features.

Oracle 8.1 and 9 were marketed with an “i” for Internet, presumably copying Apple’s product naming (Oracle 8i and 9i), Oracle 10 and 11 were marketed with a “g” for Grid Computing (Oracle 10g and 11g) and Oracle versions 12+ are marketed with a “c” for Cloud (like Oracle 12c or 21c).

Oracle Database Editions

While versions represent the evolution of Oracle Database engine development, there are also different editions of the product. The different editions include different features that allow Oracle to segment the market and charge more money for more advanced features.

The three most well-known on-premise editions in Oracle Database are

• Express (XE) - eXpress Edition, which is a limited, free to use edition.
• Standard (SE2) - Standard Edition 2.
• Enterprise (EE) – Enterprise Edition, which includes plenty of advanced features plus additional licensed packs.

You can check the Oracle features and licensing page to know which features are available on each of them.

How To Check Oracle Version And Edition

Now let’s view some methods to get information about the Oracle Database version and edition.

Querying The v$Version Dynamic View

If we can connect to the database, then the simplest way to find the Oracle version and edition is to query the V$Version view, using the following syntax:

SELECT BANNER, BANNER_FULL FROM v$version;

The banner column will display the edition and the “base” version (18.0.0.0, 19.0.0.0 or 21.0.0.0) and the banner_full column (introduced in Oracle 18c) will display the full release information.

If you test this on Oracle Live SQL (a free to use, online Oracle database site) you will get the following results:

The 14 in the version number above means that is the 11th quarterly update after the initial release, which was 19.3, released in the third quarter of 2019.

v$Version Data Before 18c

Versions older than 18c include multiple rows in the v$Version view, the important one is the one starting with “Oracle Database”:

SELECT * FROM v$version;

| Banner |
| — |
| Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production |
| PL/SQL Release 11.2.0.2.0 – Production |
| CORE 11.2.0.2.0 Production |
| TNS for Linux: Version 11.2.0.2.0 – Production |
| NLSRTL Version 11.2.0.2.0 – Production |

Querying v$Instance Dynamic View

This view contains a lot of information about the database instance we are connected to, including two columns version and version_full

SELECT version, version_full FROM v$instance;

Note: Versions older than 18c do not have the VERSION_FULL column, and VERSION includes the full version information.

Warning: You need special permissions to SELECT from the v$Instance view, so not all users may be able to query this view.

Querying Product_Component_Version View

The view PRODUCT_COMPONENT_VERSION includes information about the Oracle Database and additional components installed. It has four columns, and contains information on the product name, Edition, base version, full version, and the status of the release.

SELECT * FROM PRODUCT_COMPONENT_VERSION;

Note: Versions older than 18c do not have the VERSION_FULL column, and VERSION includes the full version information.

Finding Oracle Version At Connection Time

Some tools like SQL*Plus will show a message when connecting to a database:

$\> sqlplus / as sysdba
    
    SQL\*Plus: Release 19.0.0.0.0 - Production on Thu Sep 22 09:18:53 2022
    Version 19.3.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.14.0.0.0

In this example, since we are connecting from a computer where the Oracle client has not been updated, we will see that the SQL*Plus version reported is 19.3.0.0 (shown in the second line), while the database we are connecting to is 19.14.0.0 (shown in the sixth line).

How to know the version without connecting to the database

There may be situations where we cannot connect to the database, for example after a server failure that does not allow the database to be started. In such scenarios, if we do have access to the database server we can read the alert log file. This file contains information about important events and issues, including startup or shutdown requests. After each startup request you will see some lines like this.

Starting ORACLE instance (normal) (OS id: 15192)
2021-10-08T20:32:04.838992-03:00
All SGA segments were allocated at startup
Using an SGA granule size of 16MB
...
...
NOTE: initializing MDS service with 1 containers
**Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production**
**Version 21.3.0.0.0.**
Windows NT Version V10.0 OS Build 19043
ORACLE\_HOME = C:\Oracle\product\21c\dbhomeXE

This way you can positively identify the correct edition, version and release or quarterly update even without connecting to the database.

Wrap Up

At this point you should be able to find the version and edition of your Oracle database in a way that works for you.

If you want to try out some of these commands you should try them in Beekeeper Studio, probably the nicest to use Oracle GUI on the market.