How can sqlplus knows where ORACLE_SID point?

Question

As I understand, SID is unique value to identify an Oracle database instance.

And I can set default database instance sqlplus use by changing ORACLE_SID environment variable.

But how can sqlplus know where the SID point?

I have thought tnsnames.ora let sqlplus know the information but there seems not present that kind information. (If so when does sqlplus use tnsnames.ora?)

• miracle173: where do you start sqlplus: on the server where the database instance is running or on a different server. What kind of information do you think that sqlplus needs?
• user1822: I think ORACLE_SID only works for connections to a locally running Oracle instance.
• SangminKim: @miracle173 I run the sqlplus in a client side not a server, I thought sqlplus try to connect to the server having the SID in a client side by ORACLE_SID, is it wrong ?
• SangminKim: @a_horse_with_no_name If ORACLE_SID only works for connections to a locally running Oracle instance, How can I connect to a specific server from client side with sqlplus ?
• user1822: docs.oracle.com/database/121/SQPUG/ch_three.htm#SQPUG352

Answer 1

If you are in SQL plus and just want to know what instance or database you are connected to, you can use the following:

SELECT sys_context('USERENV','INSTANCE_NAME') FROM dual;
SELECT sys_context('USERENV','DB_NAME') FROM dual;

Answer 2

When you use SQLPlus and make a bequeath connection (e.g., not going through the listener and not specifying the SID/Service Name in the connect string or using a TNS alias), SQLPlus/Oracle uses the combination of ORACLE_SID and ORACLE_HOME to uniquely identify the instance that it connects to.

Important to note: You may have multiple instances running on one server which all share the same OH, but they will have different SIDs. Or you could have multiple instances running from multiple Oracle Homes. Two instances on one machine will never share the same combination of SID and OH.

It's important to note that this connection method will only work when you're making connections from the command line of the server that the Oracle database/listener is running on.

If you're connecting from a remote server (or from the same server if you need to connect to a specific service - e.g. a PDB in 12c), you must specify in the connect string one of either a tnsnames alias, or the hostname [optional: port] and the service name you're connecting to.

e.g,:

sqlplus scott/tiger@hr

This assumes the tnsnames.ora on the client is set up with an alias for "hr". This TNS alias will have the hostname, port, and SID/Service Name in it.

sqlplus scott/tiger@hostname[:port]/servicename

This connects to the hostname and SID/Service Name provided on the command line. ORACLE_SID, ORACLE_HOME variables and TNS are entirely unrelated to this connection and are not used.

A real example:

sqlplus hr/[email protected]:1521/hrdb 

Answer 3

The workings of how SQLPlus connects to the database without a listener is not documented, so any answer given here would be speculation.