Saturday, June 25, 2011

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:

 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:

1 comment:

  1. Wow what a weblog i am so happy to here can you more discuss here, i am back again again to your site as soon as possible and i have lot of collection for you just click here for more information.
    Technewsinfo
    Technews
    Tech
    Tech news

    ReplyDelete