Oracle Version

Requirement is simple; you want to retrieve/recover the Version Information of your currently installed Oracle Database. Let us discuss most of the ways of doing so in Oracle & SQL Server.

Here are some of the ways to get it:

1) DBA_REGISTRY (Table) 

     SELECT * FROM DBA_REGISTRY;

SELECT comp_id, comp_name, version, status, namespace, control FROM DBA_REGISTRY WHERE comp_name LIKE '%Oracle%' ORDER BY comp_name;

This will give you the db version along with all the other oracle components installed.

Here is the result:

COMP_ID	COMP_NAME	VERSION	STATUS	NAMESPACE	CONTROL
CATALOG	Oracle Database Catalog Views	9.1.0.3.0	VALID	SERVER	SYS
CATPROC	Oracle Database Packages and Types	9.1.0.3.0	INVALID	SERVER	SYS

2)      V$VERSION (View)

SELECT * FROM V$VERSION

This is the result:

BANNER

Oracle Database 9G Enterprise Edition Release 9.1.0.3.0 - 64bi

PL/SQL Release 9.1.0.3.0 - Production

NLSRTL Version 9.1.0.3.0 - Production

3)      DBMS_DB_VERSION.VERSION (Procedure)

In case you are using SQL Assistant from Oracle you need the Server output to be set.

SET SERVEROUTPUT ON

EXEC DBMS_OUTPUT.PUT_LINE (DBMS_DB_VERSION.VERSION);

EXEC DBMS_OUTPUT.PUT_LINE (DBMS_DB_VERSION.RELEASE);

In case you are using 3rd Party Vendors for SQL Assistant as Benthic Golden Software you can have an output like this:

Press F10 or select View à DBMS OUTPUT Window

Then run the above code by F7. You will have the DBMS_OUTPUT.PUT_LINE generated output in the following window:

The result of the above query will be as:

4)      DBMS_UTILITY.DB_VERSION (Function)

DECLARE

mydb_version       varchar2 (200);

mydb_compatibility varchar2 (200);

BEGIN

dbms_utility.db_version( mydb_version, mydb_compatibility );

dbms_output.put_line( mydb_version );

dbms_output.put_line( mydb_compatibility );

END;

Here is the result using Benthic Golden Software.

 5)      DATABASE_PROPERTIES (Table)

SELECT * FROM database_properties WHERE property_name LIKE '%RDBMS%';

Here is the result:

PROPERTY_NAME	PROPERTY_VALUE	DESCRIPTION
NLS_RDBMS_VERSION	9.2.0.3.0	RDBMS version for NLS parameters

SQL Server Version Information

1)     

            1) SERVERPROPERTY()

SELECT SERVERPROPERTY ('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

GO

The result will be something like this:

10.0.1600.1

CTP

Developer Edition

SERVERPROPERTY can be used to extract other data as well including COLLATION, BUILD etc.

Refer to the following link for more details:

http://msdn.microsoft.com/en-us/library/ms174396.aspx 

 2) @@VERSION

SELECT @@VERSION

The result will be something like this:

Microsoft SQL Server 2008 (SP1) - 10.0.2411.0 (X64)   Feb 21 2009

1:08:36   Copyright (c) 1988-2008 Microsoft Corporation Express

Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600)

Refer to the following link for more details:

http://support.microsoft.com/kb/321185/