ThatJeffSmith

Oracle Quick Hit: You Can Bypass TNSNames

Ever spin up a database for some quick and dirty work, and you JUST WANT TO CONNECT (!!) without going through the whole ‘add the service to your local TNSNames.ORA’ rigmarole (Oracle Net Configuration Assistant)? Oracle’s Net Configuration Assistant was one of the first JAVA powered GUIs I had run into as an Oracle user. It has been improved greatly over the past few years, but I’m pretty sure there are still much faster ways to connect to the database.

Yes, you can manage your TNSNames.ORA file manually using a text editor.

But I’m not here to talk about that. I’m here because I’m really, really lazy. I know where the database is, and I just want to connect to it. NOW.

EZCONNECT

When you just want to connect, you can put all of the connectivity information in your connection string, no need to interface with TNS. AskTom was talking about this waaaay back in 2005, and I know Oracle has supported it since at least the 8i release.

syntax

CONNECT username/password@[//]host[:port][/service_name]

The aptly named ConnectionStrings.COM has several examples, and of course don’t forget to consult the official Oracle DOCS.

Ok, I get how that works with SQL*Plus, but what about GUI tools?

Let’s use SQL Developer as an example, only since that applies to about 2 or 3,000,000 of you out there. On the connection panel, there is simply an option for ‘Direct’ connections.

This is the default behavior in SQL Developer, you have to 'work' to get to TNS

Other TNS Tricks

Managing your connections can get to be a pain. Here are a few other tricks I’ve picked up over the years that might help you too!

  • TNS_ADMIN – Local registry value that tells Oracle WHERE to find your TNSNAMES.ORA file. You can use this to point to a network location. Set up new instances in one file, and it’s auto-propogated to all your users in the organization. If your org is using this today, and you want to connect to a database not listed yet, use DIRECT connect in the mean time.
  • Of course you may not be a Windows user, that’s OK – the TNS_ADMIN also works as a *NIX shell variable
  • RAC – You can add separate entries for each Node in your cluster. If you want to connect to Node2 no matter what the cluster thinks, just add an entry for the instance on Node2. This bypasses RAC’s load balancing responsibilities, so proceed with caution.
  • Multiple Client Installs – You can install as many Oracle clients on your machine as you want. Be forewarned that many Oracle tools default to using the first client they find in the registry or in the OS PATH variable. This is handy if you want to connect to an Oracle 8 database, or if you need both 32 and 64 bit clients.