Thank you for this hint, it is exactly what I looked for.

This site itself is pretty great, and the search engine is neat!!
This solution in particular was something that will save a lot of my hair...!!

Sometimes you might want more info than you can get from global_name, for example the instance and the hostname for the database. Problem with that is that the user may not have access to the v$ tables. I use a modified version of your script which first gets the info from global_name and then tries to get info from v$instance for example. If the user doesn't have access to v$instance at least the global_name info will be displayed. If you simply use your script (but replace global_name with v$instance), when you switch from a database where you do have access to v$instance to one where you don't, the login will work but you will display the login details from the first database. Know what I mean?

Here is an example of the modified script anyway:

column global_name new_value gname
set termout off
select lower(user) || '@' ||
decode(global_name, 'ORACLE8.WORLD', '8.0', 'ORA8I.WORLD',
'8i', 'ORA8IDEV.WORLD', 'dev8i', global_name ) global_name from global_name;
select lower(user) || ':'|| instance_name ||'@'|| host_name global_name from v$instance;
set termout on
set sqlprompt '&gname> '

angus:TRFG@angustest> @connect angus/x@pan1
Connected.
angus:pan1@PANSYS01> @connect noddy/noddy@pan1
Connected.
noddy@PAN1.US.ORACLE.COM>

Hi,

I've placed the connect.sql in the same directory where I have glogin.sql (using Oracle 9i). But for some reason, when I try to execute @connect, I get the error message - connect.sql is not found.

How can I set the default directory to be ora9i\sqlplus\admin.

Please suggest

Tom,
If I do a sqlplus /nolog, login.sql asks for a value for gname since I am not yet connected to any database. Is there a workaround?
Thanks

Additionally how can I know which mode the server is running in i.e Dedicated or Shared ??? Any help will be most appreciated


Regards,

Dhimant Antani

One more problem with connect as sysdba:

idle> @connect sys/xyz@trng as sysdba
ERROR:
ORA-28009: connection to sys should be as sysdba or sysoper

Thanks...

10g sqlplus will re-read the login file, so no need for @connect now hopefully

correct

ops$tkyte@ORA10G> connect scott/tiger
Connected.
scott@ORA10G>



 

Tom
I wonder if I could use 'ora_database_name' in place of 'global_name'

rmurali@ORA9R2> select global_name from global_name;

GLOBAL_NAME
------------------------------------------------------
ORA9R2.WORLD

Elapsed: 00:00:00.00
rmurali@ORA9R2> select ora_database_name from dual;

ORA_DATABASE_NAME
------------------------------------------------------
ORA9R2.WORLD

Elapsed: 00:00:00.00
rmurali@ORA9R2>

Actually, my question is -
what is the difference between using ora_database_name and global_name

Thanks.

ora_database_name is a function that returns the global name.

 
ops$tkyte@ORA9IR2> exec dbms_output.put_line( ora_database_name );
ORA9IR2.US.ORACLE.COM
 
PL/SQL procedure successfully completed.


global_name is a column in a table.

ops$tkyte@ORA9IR2> desc global_name;
 Name                                 Null?    Type
 ------------------------------------ -------- -------------------------
 GLOBAL_NAME                                   VARCHAR2(4000)
 
ops$tkyte@ORA9IR2> select * from global_name;
 
GLOBAL_NAME
------------------------------------------------------------------------
ORA9IR2.US.ORACLE.COM


yes, you can. 

Thanks a lot Tom for shareing such a usuful information with us.