How to Check Oracle Database Version?

Oracle is a relational database management system by Oracle Corporation. It is one of the most widely used databases in OLTP, Data warehouse. Oracle released a good number of versions since 1979 and the latest version is 19C which is available for on-premise as well as in the cloud.

Many of the users are still using 11g, 12c, and 18C. So Let’s see which version of the database you are running?

Internally, Oracle maintains versions as five numeric segments (XX.X.X.X.X) divided by periods. e.g. for 10G version could be 10.1.0.1.0 or10.1.0.1.1.

Oracle database version

Meaning of each segment,

  • First numeral – Major database release number e.g 12c, 18c.
  • Second numeral – Database maintenance release number.
  • Third numeral – Application server release number.
  • Fourth numeral – Component-specific release number.
  • Fifth numeral – Platform-specific release number.

How to Check Oracle Version

Oracle maintains version information in V$VERSION, PRODUCT_COMPONENT_VERSION data dictionary Views, or Metadata Table. Just run a query against these views to find out the database version.

  • V$VERSION – Version numbers of core library components in the Oracle Database.
  • PRODUCT_COMPONENT_VERSION – Version and status information for component products

1. SQL query to get database version

You can run on SQL* Plus or just copy query and run on SQL Developer.

Query 1:-

COL BANNNER FORMAT A200
COL CON_ID FORMAT 99

SELECT * 
  FROM v$version;

BANNER                                                                           CON_ID
-------------------------------------------------------------------------------- ------
Oracle Database 10g Enterprise Edition Release 10.1.0.1.0 - 64bit Production          0
PL/SQL Release 10.1.0.1.0 - Production                                                0
CORE    10.1.0.1.0      Production                                                    0
TNS for Linux: Version 10.1.0.1.0 - Production                                        0
NLSRTL Version 10.1.0.1.0 - Production                                                0

Query 2:-

COL PRODUCT FORMAT A50
COL VERSION FORMAT A20
COL STATUS FORMAT A40

SELECT * 
  FROM product_component_version;

PRODUCT                                            VERSION              STATUS
-------------------------------------------------- -------------------- ----------------------------
NLSRTL                                             10.1.0.1.0           Production
Oracle Database 10g Enterprise Edition             10.1.0.1.0           64bit Production
PL/SQL                                             10.1.0.1.0           Production
TNS for Linux:                                     10.1.0.1.0           Production

The output of the above query is 10.1.0.1.0. That means you are running 10G Version.

2. Use dbms_utility.db_version package to check oracle version

Another method is using dbms_utility.db_version package available in the database. It is PL/SQL package, so you need to run the below script to get version information.

dbms_utility.db_version returns version and compatibility information.

PL/SQL Script to get DB Version in Oracle,

DECLARE
v1 VARCHAR2(100);
v2 VARCHAR2(100);
BEGIN
dbms_utility.Db_version (v1, v2);

dbms_output.Put_line('Version ' || v1);

dbms_output.Put_line('Compatability ' || v2);
END;

Output

Version 19.0.0.0.0
Compatability 19.0.0

It is 19C.

Summary

Knowing the version information is useful when you open a support request or when a patch needs to be applied. It also helps developers to understand the features available in the release.

I hope you found this small article helpful. Please share and subscribe.
Reference: