RE: Instance Name in Sql Prompt

[IT - Database (Do Not Use)]

I got this from someone on the list.  It shows server, instance and username.  Put it in your login.sql

 

set sqlprompt 'SQL> '

 

column SQLQueryDatabase   new_value SQLPromptDatabase   noprint
column SQLQueryTablespace new_value SQLPromptTablespace noprint
column SQLQueryUsername   new_value SQLPromptUsername   noprint
column SQLQueryHost       new_value SQLPromptHost       noprint

 

                                /* 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
/

 

                                /* 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   clear
column SQLQueryHost       clear
column SQLQueryTablespace clear
column SQLQueryUsername   clear

 

input --
del 1 last

 

set termout on
set pages 9999

-----Original Message-----
From: Ramon Estevez [mailto:[EMAIL PROTECTED]]
Sent: Thursday, October 25, 2001 8:35 AM
To: Multiple recipients of list ORACLE-L
Subject: Instance Name in Sql Prompt

Hi Friends,

 

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.

 

I mean something like that.

 

DBA1 >

DBA2 >

DB01 >

 

Regards,

 

Ramon E. Estevez

[EMAIL PROTECTED]

Dominican Republic

809-565-3121