4

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?)

5
  • 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?
    – miracle173
    Jan 16, 2016 at 20:57
  • I think ORACLE_SID only works for connections to a locally running Oracle instance.
    – user1822
    Jan 16, 2016 at 23:11
  • @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
    Jan 17, 2016 at 3:34
  • @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 ?
    – SangminKim
    Jan 17, 2016 at 3:38

3 Answers 3

2

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;
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 
-1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.