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

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 Received on Tue May 06 2003 - 12:20:20 CDT