Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?)
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;
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
The workings of how SQLPlus connects to the database without a listener is not documented, so any answer given here would be speculation.
ORACLE_SIDonly works for connections to a locally running Oracle instance.sqlplustry to connect to the server having the SID in a client side by ORACLE_SID, is it wrong ?ORACLE_SIDonly works for connections to a locally running Oracle instance, How can I connect to a specific server from client side withsqlplus?