0

I normally connect to my Oracle database at work using a connection string. Other then the obvious answer of asking the DBA what it is, using SQL Developer or some other tool can I find out what the tns listener name is. This is an excerpt from a config file in .net where i use to connect:

DATA SOURCE=myURL:port/servicename;PASSWORD=password

1

The instance's TNS name would be defined in the tnsnames.ora file on your system. When you bust that file open, you'll see something like...

MyOracleInstanceTNSName =
 (DESCRIPTION = 
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.0)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = ORA12)
 )
)

The instance name (defined in the file) is really arbitrary; meaning, you can name it whatever you want. So, in the example above, I named it MyOracleInstanceTNSName, but I could've named it something else.

In the professional world, we typically use the same name across hosts, but that's a convention. This way as we share scripts or talk about instances, we're using the same name. That's a while lot easier than remembering that Bob's "foobar" instance is the same as your "bigSexy" instance.

You can find this file in ${ORACLE_HOME}/network/admin on most systems. You should be able to find (or define) the tnsname for the instance you're using there.

  • Thank you! Unfortunately I am the developer that has just the connection string and no access to the box itself. I am connecting via SSIS and for some reason I couldnt get the versions of ODAC correctly to connect. One option was connecting via TNS Name and I was going to try that. The dba's didnt know the TNS names so I was trying to see if SQL Developer stored the TNS names somewhere: example in a file it shows that myurl.,domain.com really is MyInstanceName. Thanks – logixologist Apr 22 at 14:32
  • 1
    Have the DBA's check the ${ORACLE_HOME}/network/admin on the system you're be deploying the package to. Since the name is arbitrary, you can't reliably deduce it from the connection string alone. – Adam Apr 22 at 14:41
  • 1
    Lol! Or, "%ORACLE_HOME%\network\admin" since you're probably pushing those SSIS packages to a Windows box. – Adam Apr 22 at 16:22

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.