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, Sep 19 '08 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, Sep 19 '08 at 11:37

score 297 · Accepted Answer · answered Sep 19 '08 at 11:29

297

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 '08 at 11:29

Tony Andrews

129,880
21
220
259

2

All versions of Oracle I have ever used. I can't speak for Oracle 5.0 and before! – Tony Andrews Sep 19 '08 at 15:49
1

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 – sugardaddy Feb 23 '15 at 14:51
1

Doesn't work if you don't have permission to v$views. [Lawrence's answer](http://stackoverflow.com/a/101248/345659) has an answer for that – JumpingJezza Mar 09 '16 at 02:43
@TonyAndrews: What is the output like when select * from v$version; fails? – Atmesh Mishra Jul 19 '16 at 11:57
@AtmeshMishra: I'm not sure - maybe `ORA-00942: table or view does not exist`? What do you get? – Tony Andrews Jul 20 '16 at 08:41
@TonyAndrews : I can't replicate a failed situation! Thats why I am asking around! :) – Atmesh Mishra Jul 20 '16 at 10:10
When I run this I get `ERROR 1146 (42S02): Table 'mysql.v$version' doesn't exist`. Any ideas what might be causing this? – ptushev Nov 05 '20 at 21:36
@PauseUnpause It looks like you are using MySQL and not Oracle? – Tony Andrews Nov 06 '20 at 09:58
@TonyAndrews yes. Is there any universal command to return the type of database I am using, and the version of SQL? – ptushev Nov 06 '20 at 10:40
@PauseUnpause No I'm afraid not – Tony Andrews Nov 06 '20 at 10:51

score 49 · Answer 2 · edited Nov 21 '16 at 07:03

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 '16 at 07:03

user272735

10,473
9
65
96

answered Sep 19 '08 at 11:41

Lawrence

537
3
3

great... i needed product_component_version.. as i didnt have access to v$ views – ShoeLace Mar 03 '14 at 13:38
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! – jpaugh Sep 19 '16 at 20:14

Ugur · Answer 3 · 2013-10-23T12:38:21.300

31

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

edited Oct 23 '13 at 12:38

answered Nov 15 '11 at 11:35

Ugur

426
4
4

1

Best answer since it just gives the version number only, hence no need to parse the output to extract version in an automated script. – pseudocode Jan 10 '13 at 00:03
@omeinush Works perfectly with me (11.2.0.3 ). – collapsar Jun 14 '16 at 10:09
@tjati It appears to be dependent not on the version, but on the user permissions. `V$INSTANCE` is apparently not globally available by default. – jpmc26 Sep 21 '18 at 16:28
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 – Draif Kroneg Feb 18 '21 at 08:25
Requires dba privilege. – Marcel Stör Apr 23 '21 at 09:14

score 8 · Answer 4 · answered Sep 19 '08 at 11:38

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 '08 at 11:38

Peter Lang

54,264
27
148
161

score 3 · Answer 5 · answered Apr 11 '14 at 01:33

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 '14 at 01:33

user3362908

433
5
16

score 3 · Answer 6 · answered Oct 13 '20 at 08:42

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 '20 at 08:42

santosh tiwary

598
6
4

score 2 · Answer 7 · edited Sep 19 '16 at 20:26

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 '16 at 20:26

jpaugh

6,634
4
38
90

answered May 03 '16 at 07:23

Jack

510
3
6
22

Pancho · Answer 8 · 2018-12-04T21:03:55.313

0

The following SQL statement:

select edition,version from v$instance

returns:

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

(select privilege on the v$instance view is of course necessary)

edited Dec 04 '18 at 21:03

answered Dec 04 '18 at 18:15

Pancho

2,043
24
39

@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 https://stackoverflow.com/a/8135737/3051627 above. (are you 100% sure the user has access to v$instance on your database?) – Pancho Sep 15 '20 at 13:10
SQL> select edition,version from v$instance; select edition,version from v$instance * ERROR at line 1: ORA-00904: "EDITION": invalid identifier – Prokhozhii Sep 15 '20 at 13:32
https://docs.oracle.com/cd/E18283_01/server.112/e17110/dynviews_2002.htm – Prokhozhii Sep 15 '20 at 13:51

score 0 · Answer 9 · answered Apr 16 '19 at 15:08

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 '19 at 15:08

Lova Chittumuri

2,994
1
30
33

score 0 · Answer 10 · answered Sep 15 '20 at 16:01

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 '20 at 16:01

Prokhozhii

622
1
8
12

score -1 · Answer 11 · edited Dec 30 '13 at 16:57

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.

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

11 Answers11

Linked