Locate what oracle db version connected to

[nzcam]

Does anyone know any commands that can retreive the oracle version # of a db you are connected to.

 

[SantaMufasa]

One way to determine version is when you connect via SQL*Plus, you will see two version banners: 1) The version of SQL*Plus you are using to connect, then 2) The version of the database to which you are connecting. For example:

D:\oracle\ora92\bin>sqlplus

SQL*Plus: Release 9.2.0.4.0 - Production on Wed May 25 15:08:48 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter user-name: <username>/<password>@<tns_alias>

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production

SQL>

If you are already connected to SQL*Plus and you have at least SELECT access against the data dictionary (DBA) views, then you can issue this query:

SQL> select version from v$instance;

VERSION
-----------------
8.1.7.3.0

Let us know if these methods resolve your need.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[nzcam]

V$version is the best for what I wanted,

We support 98 apps all on different oracle versions. The 1st answer workd well when you 1st log on via sql , but when you are conn all day into different versions that does not help,

v$instance did not have version field,
Fields are Key, Value

Thanks

 

[alexhu]

v$instance did not have version field,
Fields are Key, Value

Interesting, in both versions that I use (8.0.6 and 9.0.1) the column is 'BANNER'

Alex

 

[alexhu]

OOPs - thats v$version :-(

But I get the same as Dave if I do it properly

Alex

 

[SantaMufasa]

NZCam,

Yes, V$Version is fine, as well. I don't know why you see what you see for V$Instance, because this is the structure of V$Instance on every Oracle instance I have:

SQL> desc v$instance
 Name                    Null?    Type
 ----------------------- -------- ------------
 INSTANCE_NUMBER                  NUMBER
 INSTANCE_NAME                    VARCHAR2(16)
 HOST_NAME                        VARCHAR2(64)
 [b]VERSION                          VARCHAR2(17)[/b]
 STARTUP_TIME                     DATE
 STATUS                           VARCHAR2(7)
 PARALLEL                         VARCHAR2(3)
 THREAD#                          NUMBER
 ARCHIVER                         VARCHAR2(7)
 LOG_SWITCH_WAIT                  VARCHAR2(11)
 LOGINS                           VARCHAR2(10)
 SHUTDOWN_PENDING                 VARCHAR2(3)
 DATABASE_STATUS                  VARCHAR2(17)
 INSTANCE_ROLE                    VARCHAR2(18)

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.