How to Connect to Oracle Using Service Name Instead of Sid

How to connect to Oracle using Service Name instead of SID

http://download.oracle.com/docs/cd/B28359_01/java.111/b31224/urls.htm#BEIDHCBA

Thin-style Service Name Syntax

Thin-style service names are supported only by the JDBC Thin driver. The syntax is:

@//host_name:port_number/service_name

For example:

jdbc:oracle:thin:scott/tiger@//myhost:1521/myservicename

So I would try:

jdbc:oracle:thin:@//oracle.hostserver2.mydomain.ca:1522/ABCD

Also, per Robert Greathouse's answer, you can also specify the TNS name in the JDBC URL as below:

jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL=TCP)(HOST=blah.example.com)(PORT=1521)))(CONNECT_DATA=(SID=BLAHSID)(GLOBAL_NAME=BLAHSID.WORLD)(SERVER=DEDICATED)))

How SID is different from Service name in Oracle tnsnames.ora

Quote by @DAC

In short: SID = the unique name of
your DB, ServiceName = the alias used
when connecting

Not strictly true. SID = unique name of the INSTANCE (eg the oracle process running on the machine). Oracle considers the "Database" to be the files.

Service Name = alias to an INSTANCE (or many instances). The main purpose of this is if you are running a cluster, the client can say "connect me to SALES.acme.com", the DBA can on the fly change the number of instances which are available to SALES.acme.com requests, or even move SALES.acme.com to a completely different database without the client needing to change any settings.

cx_Oracle doesn't connect when using SID instead of service name on connection string

I a similar scenario, I was able to connect to the database by using cx_Oracle.makedsn() to create a dsn string with a given SID (instead of the service name):

dsnStr = cx_Oracle.makedsn("oracle.sub.example.com", "1521", "ora1")

This returns something like

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle.sub.example.com)(PORT=1521)))(CONNECT_DATA=(SID=ora1)))

which can then be used with cx_Oracle.connect() to connect to the database:

con = cx_Oracle.connect(user="myuser", password="mypass", dsn=dsnStr)
print con.version
con.close()

JDBC Thin connection string in Oracle uses both colon and forward slash

I was able to use both colon : and forward slash / for SID in connection URL and was able get the connection established.
I think in Oracle 11g we can do such thing and for Service Name only we can use is forward slash / .

Oracle SID and Service name; connection problems

ORA-12505 means your client passed a SID that the listener on the server end
didn't recognize at all.

In 10G and above You can use EZ connect without configuring the server side
like this:

sqlplus hr@liverpool:1521/DEMO

hr is the user name
liverpool is the server name
1521 is the port the listener for the DB is listening on
DEMO is the database SID

(OR)

If you still want to use tnsnames.ora, try running tnsping SID from your client.

On LINUX, You can also have ORACLE read a tnsnames.ora file from a local
path - just set TNS_ADMIN to the directory where your tnsnames.ora file is.

Otherwise, you need to configure tnsnames.ora in $ORACLE_HOME/network/admin
on the client

---

If you need to know the database SID, use this:

select sys_context('userenv','db_name') from dual;

See this URL:

Checking oracle sid and database name

---

Related Topics