How to Find out Your Oracle Database Version

Just as you sometimes need to find out your database name when connected to Oracle, you may also need to find out your Oracle database version.

Here’s a rundown of ways to identify the Oracle database version using SQL and PL/SQL.

Query V$VERSION

V$VERSION displays version numbers of core library components in Oracle. A simple SELECT * from V$VERSION returns quite a bit of detail:

SQL> select *
  2  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 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

Query V$INSTANCE

V$INSTANCE shows details of the current database instance such as host name, startup time, status, etc. The column VERSION in V$INSTANCE contains the database version.

SQL> select version
  2  from v$instance;

VERSION
-----------------
11.2.0.2.0

Use DBMS_DB_VERSION Constants

DBMS_DB_VERSION package contains Oracle version numbers and other information useful for simple conditional compilation selections based on Oracle versions.

The package contains two constants that specify the database version and release number. They are:

VERSION CONSTANT PLS_INTEGER := <RDBMS version number>;   
RELEASE CONSTANT PLS_INTEGER := <RDBMS release number>;

So in Oracle 11.2, the value of constant VERSION would be 11, RELEASE would be 2.

This information can be retrieved in a simple PL/SQL block:

begin 
  dbms_output.put_line(
         dbms_db_version.version 
      || '.' 
      || dbms_db_version.release
                      ); 
end;
/

When run:

SQL> begin
  2    dbms_output.put_line(
  3           dbms_db_version.version
  4        || '.'
  5        || dbms_db_version.release
  6                        );
  7  end;
  8  /
11.2

PL/SQL procedure successfully completed.

Summary

This article showed three ways of finding your Oracle database version:

1. Query V$VERSION
2. Query V$INSTANCE
3. Use DBMS_DB_VERSION Constants