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

Home -> Community -> Usenet -> c.d.o.misc -> Re: Displaying DB name at SQLPlus prompt- even after conn/disc, and not SYS user ?

Re: Displaying DB name at SQLPlus prompt- even after conn/disc, and not SYS user ?

From: Bricklen Anderson <banderson_at_PresiNET.com>
Date: Tue, 06 May 2003 17:40:44 GMT
Message-ID: <3EB7F53A.DD57A737@PresiNET.com>


Thomas T wrote:
>
> So there's this trick:
>
> column my_db new_value my_db noprint
> select user || '@' || name "my_db" from v$database;
> set sqlprompt "&my_db> "
>
> But... when I'm flipping between oracle servers, using disconnect and
> connect, is there a way that this can change, too? I don't want to have to
> exit sql*plus between connections just so the above script can run. Also,
> how can I make this work if I'm -not- using the sys account? Do I need to
> grant select access on v$database- and isn't that a "bad" thing? :)
>
> And while I'm compiling this wish list; can I get the local service name,
> instead of the database name, out of Oracle? I searched the web, but I'm
> getting tons of links that talk about o/s command prompts, and system
> services...
>
> Thanks!
>
> -Thomas

to get it to refresh each new instance you connect to, you'll have to re-run the script.
Create the glogin.sql file in both bin and sqlplus/admin directories (or call it connect etc. in your bin if you wish, rather than glogin), and simply call it like @glogin from the sqlplus prompt each time you connect elsewhere.

The following is what I use (adapted somewhat from Tom Kyte's version), just add to the glogin script (or login.sql etc) It displays the sid,serial,user and instance like so: '32,39258' brick_at_DB07>

set termout off
column sid new_value spid
column global_name new_value gname
select ''''''||sid||','||serial#||''''''||' '||lower(username) sid FROM v$session
WHERE audsid = userenv('sessionid');
select substr(host_name,1,5) global_name from v$instance; set termout on
set sqlprompt '&&spid@&&gname>'

hth Received on Tue May 06 2003 - 12:40:44 CDT

Original text of this message

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