Re: Easy SQL question

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: Thu, 4 Jun 1998 14:08:01 +0200
Message-ID: <6l62ig$on5$1@hermes.is.co.za>

Terence T wrote in message <01bd8ee1$ef94e3e0$76070006_at_pgntw90>...
>> What is the command in SQL and SVRMGRL on how to determine which instance
>> you are connected to?

>2 ways (maybe more):
>select * from v$database;
> OR
>select * from global_name;

That is OK if you're not running multiple OPS instances (i.e. multiple instances, same database). All OPS instances will return the same value (i.e. the database SID and not the instance/machine name you're on) for the above SQLs.

I'm not sure if there's a better way, but I rely on the v$session table - I look at the MACHINE column specified for the Oracle system process (sid 1). I'm not sure that this will work on NT, but it works fine on 7.3.x on Unix.

Package Header Def:
FUNCTION get_instance RETURN VARCHAR2; PRAGMA RESTRICT_REFERENCES( get_instance, WNDS );

Package Body Def:
FUNCTION get_instance RETURN VARCHAR2 IS   instance VARCHAR2 (20);
BEGIN
  SELECT machine INTO instance FROM v$session WHERE sid = 1;   RETURN instance;
END; You can add the above into any of your homegrown sysdba/developer packages and then do something like:
SELECT
  sys.mypackage.get_instance
FROM dual

Or use it as a funtion in any other SQL statement. I'm using it for my homegrown performance db - no g$ views for me until they decide we can upgrade to Oracle 8. :-)

Hopes this is of some help.

regards,
Billy Received on Thu Jun 04 1998 - 07:08:01 CDT