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

"Bricklen Anderson" <banderson_at_PresiNET.com> wrote in message news:3EB7F53A.DD57A737_at_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

Thanks for the info Brian; I like the extra info you put into the prompt. But I should've been a bit more clear... besides not wanting to exit sql*plus, I don't want to re-run a script every time I connect/disconnect. I guess I can write a script called con.sql, that goes something like:

connect &a
select 'prompt-changing query here' from dual;

But that's still not my ideal solution. Oh well.

Thanks,

-Thomas Received on Tue May 06 2003 - 19:19:45 CDT