RE: Instance Name in Sql Prompt

From: IT - Database (Do Not Use) <dbamail_at_police.edmonton.ab.ca>
Date: Thu, 25 Oct 2001 07:27:23 -0700
Message-ID: <F001.003B4409.20011025071546@fatcity.com>

I got
this from someone on the list.  It shows server, instance and username.  Put it in your login.sql
<SPAN
class=187481414-25102001> 
set
sqlprompt 'SQL> '
 

column
SQLQueryDatabase   new_value SQLPromptDatabase   noprintcolumn SQLQueryTablespace new_value SQLPromptTablespace noprintcolumn SQLQueryUsername   new_value

SQLPromptUsername   noprintcolumn 
SQLQueryHost       new_value 
SQLPromptHost       noprint

 

<SPAN

class=187481414-25102001>                                

/* remove ".WORLD"; append "."

just                                 

* in case it is

absent                                 

*/select

initcap(substr(g.GLOBAL_NAME,                      
1,                      

instr(g.GLOBAL_NAME ||

'.',                            

'.') - 1)) SQLQueryDatabase,      

u.DEFAULT_TABLESPACE            
SQLQueryTablespace,       
u.USERNAME                      

SQLQueryUsername   from GLOBAL_NAME
g,        USER_USERS 
u/
 

<SPAN

class=187481414-25102001>                                

/* this defaults the SQLQueryHost

variable                                 

* in case we have no privileges on

the                                 

* v$session

view                                 

*/select '' SQLQueryHost   from USER_USERS   where rownum = 1/
 

select

initCap(decode(s.MACHINE,                      

NULL,

'',                      

'',

'',                      

s.MACHINE || '/')) SQLQueryHost   from V$SESSION s   where upper(s.PROGRAM) like 'ORACLE%.EXE' or  -- uSoft executable         upper(s.PROGRAM)
like 'ORACLE%(PMON)%'  -- Unix ps style/
 

--set
sqlprompt
'[&SQLPromptHost&SQLPromptDatabase:&SQLPromptTablespace]&SQLPromptUsername> 'set sqlprompt '[&SQLPromptHost&SQLPromptDatabase] &SQLPromptUsername> '
 

select

''    SQLQueryDatabase,       
''    SQLQueryHost,       
''    SQLQueryTablespace,       
'SQL' SQLQueryUsername   from USER_USERS/

 

column
SQLQueryDatabase   clearcolumn
SQLQueryHost       clearcolumn
SQLQueryTablespace clearcolumn SQLQueryUsername   clear
 

input
--del 1 last
 

set
termout onset pages 9999

  <FONT face=Tahoma
  size=2>-----Original Message-----From: Ramon Estevez   [mailto:com.banilejas_at_codetel.net.do]Sent: Thursday, October 25,   2001 8:35 AMTo: Multiple recipients of list   ORACLE-LSubject: Instance Name in Sql Prompt   Hi
  Friends,
  <SPAN
  class=578542714-25102001> 
  Excuse me for this dumb
  question,  how do I display the instance name in the   sql prompt.  Have
  3 DB and would like to know to which one I am connected.   <SPAN
  class=578542714-25102001> 
  I mean something like
  that.
  <SPAN
  class=578542714-25102001> 
  DBA1
  >
  DBA2
  >
  DB01
  >
  <SPAN
  class=578542714-25102001> 
  <SPAN
  class=578542714-25102001>Regards,
  <SPAN
  class=578542714-25102001> 
  Ramon E.
  Estevez
  <A
  href="">com.banilejas_at_codetel.net.do
  Dominican Republic
  809-565-3121
    Received on Thu Oct 25 2001 - 09:27:23 CDT