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?)
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/hr@productiondatabase.company.com:1521/hrdb
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;
The workings of how SQLPlus connects to the database without a listener is not documented, so any answer given here would be speculation.
