Re: ORA_SID in Sql*Plus?

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

To see your current info at the Korn Shell prompt put this "PS1..." example in your home directory .profile:

   [ksh]: PS1="[`uname -n`:`basename $SHELL`:${ORACLE_SID:-???}]: "    [prod:ksh:proddb]:

or add the following to your $ORACLE_HOME/sqlplus/admin/glogin.sql file to get a sql prompt that has the host, dbname and session id and looks like "SQL_at_prod:proddb:7>: "

  REM BOF BOF BOF BOF BOF BOF BOF BOF BOF BOF BOF   set termout off  

  column glogin_prompt1 noprint new_value _glogin_prompt1   select substr(program,instr(program,'_at_')+1,instr(program,' ')-instr(program,'@')-1)||

         ':'||value glogin_prompt1
  from v$parameter,v$process where program like '%SMON%' and name='db_name';  

  column glogin_prompt2 noprint new_value _glogin_prompt2   select sid glogin_prompt2
  from v$session where audsid = userenv('sessionid');  

  column glogin_prompt3 noprint new_value _glogin_prompt3   select '&_glogin_prompt1' || ':' || ltrim('&_glogin_prompt2') glogin_prompt3   from dual;  

  set sqlprompt 'SQL_at_&_glogin_prompt3> '  

  set pagesize 24 linesize 132 pause on pause 'Hit Enter To Continue...'   set termout on
  REM EOF EOF EOF EOF EOF EOF EOF EOF EOF EOF EOF

-- 
                       \\|//
                       (0-0)
           +-----oOO----(_)-----------+
           | Brian P. Mac Lean        |
           | Database Analyst         |
           | brian.maclean_at_teldta.com |
           | http://www.teldta.com    |
           +-------------------oOO----+
                      |__|__|
                       || ||
                      ooO Ooo