How can I confirm a database is Oracle & what version it is using SQL?

Question

I'm building an installer for an application. The user gets to select a datasource they have configured and nominate what type of database it is. I want to confirm that the database type is indeed Oracle, and if possible, what version of Oracle they are running by sending a SQL statement to the datasource.

What about your programming language? This kind of question really depends on the language API for the DB access. — gizmo
– gizmo, Commented Sep 19, 2008 at 11:29
I can assume I have a JDBC datasource. If the connection fails, or the sql statement generates and error then I can certainly trap that and treat it accordingly. — modius
– modius, Commented Sep 19, 2008 at 11:37

Tony Andrews · Accepted Answer · 2008-09-19 11:29:06Z

298

Run this SQL:

select * from v$version;

And you'll get a result like:

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Solaris: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

answered Sep 19, 2008 at 11:29

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Tony Andrews Over a year ago

All versions of Oracle I have ever used. I can't speak for Oracle 5.0 and before!

sugardaddy Over a year ago

This technique failed for me on Oracle 11.2.0.2.0, but I am having some login permission problems. However, for those that might be in the same boat as me, the second technique mentioned on this page by Lawrence did work: select * from product_component_version

JumpingJezza Over a year ago

Doesn't work if you don't have permission to v$views. Lawrence's answer has an answer for that

Atmesh Mishra Over a year ago

@TonyAndrews: What is the output like when select * from v$version; fails?

Tony Andrews Over a year ago

@AtmeshMishra: I'm not sure - maybe ORA-00942: table or view does not exist? What do you get?

|

user272735 · Accepted Answer · 2016-11-21 07:03:41Z

49

Two methods:

select * from v$version;

will give you:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Solaris: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

OR Identifying Your Oracle Database Software Release:

select * from product_component_version;

will give you:

PRODUCT VERSION STATUS
NLSRTL  11.1.0.6.0  Production
Oracle Database 11g Enterprise Edition  11.1.0.6.0  64bit Production
PL/SQL  11.1.0.6.0  Production
TNS for Solaris:    11.1.0.6.0  Production

edited Nov 21, 2016 at 7:03

user272735

10.6k9 gold badges72 silver badges100 bronze badges

answered Sep 19, 2008 at 11:41

Lawrence

5373 silver badges3 bronze badges

2 Comments

ShoeLace Over a year ago

great... i needed product_component_version.. as i didnt have access to v$ views

jpaugh Over a year ago

That second query is far more suitable for automatic checking, as it requires defines a data format, rather than relying on ad-hoc parsing. Thanks for posting!

Ugur · Accepted Answer · 2013-10-23 12:38:21Z

32

SQL> SELECT version FROM v$instance;
VERSION
-----------------
11.2.0.3.0

edited Oct 23, 2013 at 12:38

answered Nov 15, 2011 at 11:35

Ugur

4364 silver badges4 bronze badges

6 Comments

pseudocode Over a year ago

Best answer since it just gives the version number only, hence no need to parse the output to extract version in an automated script.

collapsar Over a year ago

@omeinush Works perfectly with me (11.2.0.3 ).

jpmc26 Over a year ago

@tjati It appears to be dependent not on the version, but on the user permissions. V$INSTANCE is apparently not globally available by default.

Draif Kroneg Over a year ago

It does not work on Oracle 18c: SQL> SELECT version FROM v$instance; SELECT version FROM v$instance * ERROR at line 1: ORA-00942: table or view does not exist

Marcel Stör Over a year ago

Requires dba privilege.

|

Peter Lang · Accepted Answer · 2008-09-19 11:38:44Z

8

You can either use

SELECT * FROM v$version;

or

SET SERVEROUTPUT ON
EXEC dbms_output.put_line( dbms_db_version.version );

if you don't want to parse the output of v$version.

answered Sep 19, 2008 at 11:38

Peter Lang

55.7k29 gold badges153 silver badges165 bronze badges

Comments

user3362908 · Accepted Answer · 2014-04-11 01:33:54Z

3

If your instance is down, you are look for version information in alert.log

Or another crude way is to look into Oracle binary, If DB in hosted on Linux, try strings on Oracle binary.

strings -a $ORACLE_HOME/bin/oracle |grep RDBMS | grep RELEASE

answered Apr 11, 2014 at 1:33

user3362908

4431 gold badge5 silver badges19 bronze badges

Comments

santosh tiwary · Accepted Answer · 2020-10-13 08:42:45Z

3

There are different ways to check Oracle Database Version. Easiest way is to run the below SQL query to check Oracle Version.

SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;

SQL> SELECT * FROM v$version;

answered Oct 13, 2020 at 8:42

santosh tiwary

7288 silver badges4 bronze badges

Comments

jpaugh · Accepted Answer · 2016-09-19 20:26:48Z

2

For Oracle use:

Select * from v$version;

For SQL server use:

Select @@VERSION as Version

and for MySQL use:

Show variables LIKE "%version%";

edited Sep 19, 2016 at 20:26

jpaugh

7,1845 gold badges46 silver badges94 bronze badges

answered May 3, 2016 at 7:23

Jack

5203 gold badges8 silver badges25 bronze badges

Comments

Lova Chittumuri · Accepted Answer · 2019-04-16 15:08:18Z

0

We can use the below Methods to get the version Number of Oracle.

Method No : 1

set serveroutput on;
BEGIN 
DBMS_OUTPUT.PUT_LINE(DBMS_DB_VERSION.VERSION || '.' || DBMS_DB_VERSION.RELEASE); 
END;

Method No : 2

SQL> select *
  2  from v$version;

answered Apr 16, 2019 at 15:08

Lova Chittumuri

3,4011 gold badge38 silver badges39 bronze badges

Comments

Prokhozhii · Accepted Answer · 2020-09-15 16:01:21Z

0

This will work starting from Oracle 10

select version
      , regexp_substr(banner, '[^[:space:]]+', 1, 4) as edition 
from    v$instance
     ,  v$version where regexp_like(banner, 'edition', 'i');

answered Sep 15, 2020 at 16:01

Prokhozhii

7301 gold badge8 silver badges14 bronze badges

Comments

Pancho · Accepted Answer · 2024-12-16 14:44:51Z

0

The following SQL statement:

select edition,version from v$instance

returns:

database edition eg. "XE"
database version eg. "12.1.0.2.0"

Notes:

select privilege on the v$instance view is of course necessary
the edition columnn is not available pre Oracle 12.1

edited Dec 16, 2024 at 14:44

answered Dec 4, 2018 at 18:15

Pancho

2,2131 gold badge27 silver badges42 bronze badges

5 Comments

Pancho Over a year ago

@Prokhozhii - We now use 19 so i can no longer confirm but I would appreciate it if you could provide additional explanation as select version from v$instance definitely works in Oracle 11 . See answer stackoverflow.com/a/8135737/3051627 above. (are you 100% sure the user has access to v$instance on your database?)

Prokhozhii Over a year ago

SQL> select edition,version from v$instance; select edition,version from v$instance * ERROR at line 1: ORA-00904: "EDITION": invalid identifier

Prokhozhii Over a year ago

docs.oracle.com/cd/E18283_01/server.112/e17110/…

Jeppe Stig Nielsen Dec 13, 2024 at 13:03

For Oracle 21, V$INSTANCE

Pancho Dec 16, 2024 at 14:46

@Prokhozhii thanks - in the documentation "edition" is only introduced in Oracle 12.1. docs.oracle.com/database/121/REFRN/… I have updated my comment accordingly.

Kent Pawar · Accepted Answer · 2013-12-30 16:57:51Z

Here's a simple function:

CREATE FUNCTION fn_which_edition
        RETURN VARCHAR2
    IS

    /*

        Purpose: determine which database edition

        MODIFICATION HISTORY
        Person      Date        Comments
        ---------   ------      -------------------------------------------
        dcox        6/6/2013    Initial Build

    */

    -- Banner
    CURSOR c_get_banner
    IS
        SELECT banner
          FROM v$version
         WHERE UPPER(banner) LIKE UPPER('Oracle Database%');

    vrec_banner c_get_banner%ROWTYPE; -- row record
    v_database VARCHAR2(32767); --

BEGIN
    -- Get banner to get edition
    OPEN c_get_banner;
    FETCH c_get_banner INTO vrec_banner;
    CLOSE c_get_banner;

    -- Check for Database type
    IF INSTR( UPPER(vrec_banner.banner), 'EXPRESS') > 0
    THEN
        v_database := 'EXPRESS';
    ELSIF INSTR( UPPER(vrec_banner.banner), 'STANDARD') > 0
    THEN
        v_database := 'STANDARD';
    ELSIF INSTR( UPPER(vrec_banner.banner), 'PERSONAL') > 0
    THEN
        v_database := 'PERSONAL';
    ELSIF INSTR( UPPER(vrec_banner.banner), 'ENTERPRISE') > 0
    THEN
        v_database := 'ENTERPRISE';
    ELSE
        v_database := 'UNKNOWN';
    END IF;

    RETURN v_database;
EXCEPTION
    WHEN OTHERS
    THEN
        RETURN 'ERROR:' || SQLERRM(SQLCODE);
END fn_which_edition; -- function fn_which_edition
/

Done.

Prokhozhii · Accepted Answer · 2025-01-19 20:51:54Z

-1

In addition to the answer from user3362908. If your instance is down you may use following:

$ORACLE_HOME/bin/sqlplus -V

answered Jan 19 at 20:51

Prokhozhii

7301 gold badge8 silver badges14 bronze badges

2 Comments

elmicha Apr 15 at 14:41

That shows the version of sqlplus, not the database.

Prokhozhii Apr 20 at 19:15

@elmicha. If you read original question you won't find the question about the database version but about Oracle (maybe database, maybe software): "what version of Oracle they are running".

Collectives™ on Stack Overflow

How can I confirm a database is Oracle & what version it is using SQL?

12 Answers 12

10 Comments

2 Comments

6 Comments

Comments

Comments

Comments

Comments

Comments

Comments

5 Comments

Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

12 Answers 12

10 Comments

2 Comments

6 Comments

Comments

Comments

Comments

Comments

Comments

Comments

5 Comments

Comments

2 Comments

Linked

Related