How to show instance name in SQL*PLUS?

I have two instances with Oracle 7.3 on Solaris 2.5. I want to show
startup message to indicate which instance I connected to when I
successfully login to Oracle in SQL*PLUS (on both UNIX and PC platform).
How can I do this? Moreover, I also want to show which instance the user
connected to in the SQLPLUS prompt? How can I do this?

Ans1:
We do it by including the line:

start $ORACLE_HOME/sqlplus/admin/$ORACLE_SID.sql

in $ORACLE_HOME/sqlplus/admin/glogin.sql

where, for example, the file $ORACLE_HOME/sqlplus/admin/live.sql contains

set SQLPROMPT Live-SQL>

Obviously, we have one such file for each $ORACLE_SID ( live.sql, test.sql )

Dave.

Ans2:
Isn't there a problem with this approach, ie that SQL*Plus doesn't call
glogin.sql/login.sql when the user connects (using the connect command) to
another instance within SQL*Plus, thus having a connection to instance B
while the prompt shows that (s)he is connected to instance A?

Finn

Ans3:
There is a better way?

Actually, yes and no. There are two dictionary views: v$database and
global_name that provide you with this info. The latter one is probably
available to everyone.
You could think of the following code in login.sql
column global new_value dbname
select substr(global_name, 1, instr(global_name,'.') - 1) global -- to parse of
the db_domain
from global_name;
set sqlp '&dbname'

I agree this still doesn't address the user switching databases within
sqlplus...

Best Regards,

Sybrand Bakker, Oracle DBA

Ans4:
You might try this 'login.sql'. Make sure public is granted select
privilege on SYS.V_$SESSION and SYS.V_$DATABASE and that this 'login.sql'
is the first in SQLPATH

For NT environments, just change
'/tmp/' into '\temp\',
'!' into 'ho',
'rm' into 'del',
'clear' into 'cls' and
'vi' into 'notepad'.

Cheers
Kaboel Karso

Hosted by www.Geocities.ws