Get the Oracle Database Version Number

If you’re looking for how to get the Oracle Database Version, there are lots of ways to do so, depending on what your end-goal is.

Running A Quick Query

The easiest, simplest, fastest method is to run the following query:

select *
from v$version;

This will return multiple columns, depending on what the information is that you’re looking for.

For example, the BANNER_FULL column will return something like this:

"Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0"

But there are other ways of getting the same information, too!

For example, if you don’t have privileges to v$version, you run a query against v$instance:

select version
from v$instance;                                                                                                                                                                                                                                     

This will give you a much more simplified version of the information:

18.0.0.0.0

Note that this does not give you the release number!

What if you don’t have privileges to either of those views, or neither provides you with the info you’re looking for?

Try something like this:

select * 
from product_component_version;

In Code (For Compatibility Purposes)

What if you want to write Pl/SQL code that…in one version uses one kind of functionality, but in a different version uses a different type of functionality?

I’d recommend using the DBMS_DB_VERSION package.

The simplest example of using this would be like so:

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

But what if you wanted to write code one way for one version, but a different way for a different version?

You might use Pl/SQL compiler flags.

For example, the enable_parallel_dml hint only works with 12.1 and above;

So, if I wanted to write a piece of Pl/SQL that used an enable_parallel_dml hint if 12.1 and above, or used an ALTER SESSION ENABLE PARALLEL DML; with 11.2 or lower, I could do something like this:

create table drop_me
as
select *
from dual;

set serveroutput on

begin
  -- if the version is less than or equal to 11.2, then...
  $if dbms_db_version.ver_le_11_2 $then
    -- use ALTER SESSION to change parallel dml.
    execute immediate 'alter session enable parallel dml';
    update drop_me
    set dummy = 'Q';
    execute immediate 'alter session disable parallel dml';
    
    dbms_output.put_line(q'[You're using 11.2 or lower!]');
  -- If we're NOT <= 11.2... (meaning 12.1 or above)...
  $else
    -- then just use the ENABLE_PARALLEL_DML hint.
    update /*+ enable_parallel_dml */ drop_me
    set dummy = 'Q';
    
    dbms_output.put_line(q'[You're using 12.1 or higher!]');
  $end
end;
/

Free Oracle SQL Tuning Guide

Checkout my FREE guide, 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!

Get it here: tuningsql.com/secrets