Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: which server am I on?

Re: which server am I on?

From: Robert W. Swisshelm <swisshelm_at_lilly.com>
Date: 1996/12/21
Message-ID: <32BBF64F.1A52@lilly.com>#1/1

Greg Jackson wrote:
>
> In article <59bdan$ldf_at_svna0001.clipper.ssb.com>
> tahmossx_at_itd.ssb.com (Stephen A Tahmosh) wrote:
>
> > One source of the data is: "select name from v$database;"
> > column upper(VALUE) new_value _DB_NAME
> > select upper(value) from v$parameter
> > where upper(name) = 'DB_NAME';
> > set SQLPROMPT '&_DB_NAME.> '
>
> rather than querying the db, you could just add
>
> HOST echo $ORACLE_SID
>
> to the $ORACLE_HOME/sqlplus/admin/glogin.sql file.
>
> greg jackson
> gjackson_at_pier1.com

There are a number of problems with the approach shown above.

I assumed from the original message that the SQL*PLUS sessions he was talking about were on his client machine. In that case, using a HOST command would run the command on his client, not on the server.

The echo command shown above is operating system specific. If you run on different types of servers, you would have to use different commands.

Even the original suggestion (using V$DATABASE) has a flaw. It is always possible to have a different instance name than the database name. This is particularly true if you are running parallel server. Most of the time you actually want to know the instance name, not the database name.

You can get the instance name from V$THREAD, but you have to do a little work to get it. If you are in parallel mode, there are multiple records in V$THREAD. You have to join v$thread with the v$parameter record that shows your current thread number. Theoretically, you could do the same if you are in exclusive mode. However, in that case, the value of the 'thread' parameter is 0, but v$thread.thread# is 1. So, you have to use decode.

Another advantage of using V$THREAD is that the INSTANCE field is case sensitive. In UNIX, you could have both the 'ABC' and 'abc' instances running on the same machine. V$DATABASE would show both to be 'ABC'. V$THREAD would show the actually instance name.

The code below can be used in both exclusive and parallel instance. I realize that it is not as easy as querying V$DATABASE, but it is more accurate.

select instance
from v$thread t, v$parameter p
where t.thread# = decode(to_number(p.value),0,1,to_number(p.value)) and p.name = 'thread'

-- 
Bob Swisshelm
Eli Lilly and Company
swisshelm_at_lilly.com
Received on Sat Dec 21 1996 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US