Re: ORA_SID in Sql*Plus?

In article <326EEC30.70DB_at_charlie.cns.iit.edu> nettama_at_charlie.cns.iit.edu writes:
>Douglas Scott wrote:
>>
>> Does anyone know if there is a way to find out what database instance
>> you're in from inside of Sql*Plus? I go in and out of 2 instances all
>> day long (beta & production databases) and sometimes lose track of which
>> one I'm in (especially after an interruption like a meeting). I could
>> get out and come back in, but if there is someway to tell which instance
>> it would be easier.
>>
>> Douglas Scott
>> dsscott_at_gte.net
>
>connect as system/manager (or any user with dba role)
>and do
>SELECT *FROM V$DATABASE;
>
>--
> AMARENDRA B NETTEM ( http://www.iit.edu/~nettama)
> ORACLE CONSULTANT
> WHITTMAN-HART CORPORATION
> CHICAGO.
My apologies to whoever originally posted this, as I've lost the attribution, but if you have a file like the following named login.sql in your working directory, you can have a nice prompt to remind you. This even made me go back and review the differences between dbname and instance name, I had goofed on one instance and never even noticed it.

rem /*********************************************************************
rem *
rem *   login.sql - sqlplus start up file
rem *
rem *   next four statements sets the sql prompt = oracle instance.

rem *********************************************************************/
 

column sid new_value osid noprint
select lower (substr (global_name, 1, (instr (global_name, '.') -1))) sid from global_name ;
set sqlprompt '&osid> '

rem /********** end of login.sql ***********************/
-- 
Joel Garry               joelga_at_rossinc.com               Compuserve 70661,1534
These are my opinions, not necessarily those of Ross Systems, Inc.   <> <>
%DCL-W-SOFTONEDGEDONTPUSH, Software On Edge - Don't Push.            \ V /
panic: ifree: freeing free inodes...                                   O