Getting TNS Name from connection string

Question

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

Answer 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.

• logixologist: 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
• Adam: 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: Lol! Or, "%ORACLE_HOME%\network\admin" since you're probably pushing those SSIS packages to a Windows box.