1

When I run the following command

sqlplus user/mysecretpass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=10.1.250.81)(Port=1521))(CONNECT_DATA=(SID=gisdb)))

I am able to establish connection and result is as follows

enter image description here

I am also able to connect to same database using SQL Developer but when I try to create database connection from ArcMap 10.6.1 I get the following error

enter image description here

I have also followed the instructions here, but I am unable to solve this issue. Can anyone let me know how to solve this problem?

  • There are a number of potential causes, but the most likely is not having the required environment variables. If you use the ArcPy equivalent function you might get a more explicit error. Either way, we need more than you've provided to help. – Vince May 16 at 10:34
  • In the ArcGIS connection string, you say 10.1.250.81/gisdb gisdb is a service name. In your command line sqlplus, gisdb is a sid. The best connection method is to always use service names. What happens when you do sqlplus user/mysecretpass@10.1.250.81:gisdb then sqlplus user/mysecretpass@10.1.250.81/gisdb ? Do both work ? – Albert Godfrind May 16 at 13:30
  • Forgot this to clarify: a / in the connection string means that whatever follows is a service name. a:means that what follows is a "sid" or "system ID". A "sid" identifies a physical database "instance" (= the processes that access a database). A clustered database can have multiple such instances, on multiple servers: each has its own SID. On the other hand, all instances can share the same service. This is how applications can automatically failover to a different server. – Albert Godfrind May 16 at 14:47
  • Using a service name is the proper way to connect to a database. It is the only way when you connect to a multi-tenant environment (and this is the default now for all Oracle databases, including XE). It is also the only way to connect to a cloud-served database. – Albert Godfrind May 16 at 14:48
  • Hi Godfrind, when I try to run user/mysecretpass@10.1.250.81/gisdb it gives me TNS:Listener does not know of service requested in connect descriptor and when I try sqlplus user/mysecretpass@10.1.250.81:gisdb I get connect failed because target host or object does not exist. – Asad Abbas May 17 at 4:59
2

I was able to solve this problem by adding the following entry in tnsnames.ora

test =(DESCRIPTION=ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=10.1.250.81)(Port=1521)))(CONNECT_DATA=(SID=gisdb)))

and then using the specified tnsname "test" as instance while connecting to oracle from ArcMap.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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