System variable that stores database specification?? 2

[sjh]

Hi,

Is there an Oracle system variable that stores the current database specification? As in the database_specification in the following command?

CONNECT username/password@database_specification

Thank you in advance!
Susie

 

[KenCunningham]

Do you mean the ORACLE_SID? Which OS?

 

[sjh]

I am not sure if I need ORACLE_SID or DB_NAME. How do I find the values for these two?

I am using Windows 2000.

Thanks!
Susie

 

[SantaMufasa]

CONNECT username/password@database_specification

In the above example, Susie, the term to which you refer a "database_specification" is, in fact, a "SQL*Net alias". The SQL*Net aliases available for use on your client machine appear in the "tnsnames.ora" file that typically resides in your $ORACLE_HOME\network\admin directory.

For the sake of organization, administrators typically use the name of the Oracle instance as the SQL*Net alias. But you can have an instance named "FRED" that manages a database named "BARNEY", to which we connect with a SQL*Net alias of "WILMA": "connect system/<password>@WILMA".

Once you are connected to an Oracle instance, you can confirm the name of the instance with the query:

select instance_name from v$instance;

INSTANCE_NAME
-------------
fred

You can confirm the database name with the query:

select name from v$database;

NAME
---------
BARNEY

I hope this helps. If you have follow-on questions about this, please post.




Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[sem]

As Dave already mentioned above @database_specification denotes TNS_ALIAS that may differ from ORACLE_SID and DB_NAME. AFAIK database knows virtually nothing about the name in your local TNSNAMES.ORA file as it's resolved locally. Besides you don't have to use that alias at all and may put the whole connect string (DESCRIPTION = (ADDRESS_LIST = ... ) thus in that case your question does not make sence at all. And after all without that part of connect string you're either connected to the local database if ORACLE_SID is defined or to remote one whose TNS_ALIAS is stored in LOCAL environment variable.

Regards, Dima

http://seminihin.narod.ru