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/

Comments

Post a Comment

Popular posts from this blog

Automatic Replenishment ~ Warehouse Management (WM) in SAP

Image
This article is intended for SAP functional consultants to configure automatic replenishment of a particular storage type for transfer orders. Whenever the stock in a bin falls below the MIN value (predefined minimum quantity), it will trigger replenishment or transfer request (TR) which will in turn get converted into a transfer order, so that the workers in the warehouse can carry out the actual physical transfer of stock. We have a provision of assigning a definite storage type from where the stock will be picked up for replenishment.   Step 1 : Material Master has to be maintained with Warehouse Views. Under Warehouse Mgmt 2 Storage bin, Maximum bin Quantity, Minimum bin Quantity and Replenishment Quantity has to be maintained. Configuration Changes: Step 2 : Movement type 320 is copied from existing movement type 319 under Logistics Execution ->Warehouse management->Activities->Transfers->Define Movement Types. Movement type 320 is configured as the follo...
Read more

Teradata - Month End Date

Sql Server: Calculating the last day of the month in SQL Server is pretty simple. Run the following piece of code and its done. SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) CurrentMonthEnd_Day ResultSet: CurrentMonthEnd_Day ———————– 2010-08-31 23:59:59.000 Teradata: However, with reference to Teradata SQL, in our day to day queries in a datawarehouse environment very frequently we need to play with the month end dates that are mostly kept in the date dimension table with one field, i.e. the month end date field. You might require to fetch the month end date which is 4 years back. usually the simplest way in this case will be to calculate 48 months back from the current month end date (in case the monthe end date that long is not stored into the database). This is fair. The piece of code required to do is simple. SELECT (ADD_MONTHS(CAST('2012/08/31' AS DATE),-48)) Result: 2008/08/31 Now trying this simple thing will not work if its a leap year a...
Read more

SQL Optimizer Parameterization

Image
Plan Recompilation Once the Query Optimizer has come up with a plan, which may have taken a considerable amount of work, SQL Server does its best to ensure that you can leverage all that costly work again. It does this by caching the plan it just created, and taking steps to ensure that the plan is reused as widely as possible. It does this by using parameterization options. Parameterization Parameterization is a process whereby SQL Server will take the T-SQL you entered and look for ways to replace values that may be variables with a token, so that if a similar query is processed, SQL Server can identify it as being the same underlying query, apart from some string, or integer values, and make use of the already cached plan. For example, the following is a basic T-SQL query to return data from the Adventure Works 2008 database:    select *    from person.person    where lastname = 'fluffy' The parameterization of this query would result in the string...
Read more