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.
Twitter
RSS
GooglePlus
Facebook
I Just Wanna Connect Oracle!
May 26, 2011 @ 09:59:20
[...] your application doesn’t support EZ CONNECT (bypasses TNSNAMES), make sure you installed the full Oracle Client and not the Instant Client. If [...]
Apr 12, 2012 @ 05:59:32
Good Day, i just to know if its possible to connect to Database without using username and password and how can someone do it?
thanks inandvance.
Apr 12, 2012 @ 07:56:19
In a way. You can setup Oracle to let the Operating System authenticate the user. But you’ll still need a valid network logon to get in…just not an Oracle user and password.
Apr 13, 2012 @ 05:05:33
Good Jeffs, Thank you for your comment. i have valid network log on but i don’t have user name and password to connect to oracle database . please how do i go about it?
thanks
Apr 13, 2012 @ 07:46:19
Who owns this database?
Find the person who create it or is actively managing it, and ask them to create an account for you.
If you try to get in otherwise, this is what’s known as ‘hacking’, or in non-computer terms, ‘breaking and entering.’
It’d be like me walking up to your house and trying to get in. It’s a much different story if you give me a key though.
Sorry for speaking so bluntly, but I don’t want anyone to read this exchange and be unsure of what my position is.
Jun 06, 2012 @ 12:12:26
The link to “OraFAQ has a great document” going into the nitty-gritty details with examples, goes to AskTom post. I have tried to find this document at OraFAQ.com but it has been impossible. I have also googled and the document is missing. Do you know the direct link to this document? Thanks in advance.
Jun 06, 2012 @ 14:08:57
I think I fat-fingered the OraFAQ link when I wrote the post, and now I can’t find it either. But I did find this – http://www.connectionstrings.com/oracle – and I’ve updated the post. Do you have a specific connection question? I can help. Just contact me at jeff.d.smith@oracle.com
Jun 07, 2012 @ 03:44:21
I have found the original link to EZConnect at OraFAQ.com: http://www.orafaq.com/wiki/EZCONNECT.
Dec 05, 2012 @ 09:16:51
Hey Jeff, I’m an analyst and we’re on 8i. My desktop was just updated to Windows 7 64 bit. I’ve got Oracle running in XP mode no prob. The issue is that I’m trying to run the client for my automation stuff in MS Access and Excel in Windows 7 to take advantage of the 64 bit power. I’m getting the 1114 (.dll) errors due to the fact that there are two ODBC managers and the 32 bit one isn’t finding the Oracle data it needs to complete the setup suggestions?
Dec 05, 2012 @ 09:19:57
Mind if I challenge you just a bit? What ’64 bit power’ do you think you’ll be harnessing? More addressable memory probably wouldn’t come into play…just how much data are you looking ot push to Access/Excel from Oracle, or the other way around?
‘the 1114 dll errors’ – not sure what those are. If you’re going to do 64 bit, EVERYTHING in the chain HAS TO BE 64 bit.
Dec 05, 2012 @ 09:28:03
Thanks for the quick reply…sure challenge away…the RAM comes into play when manipulating the views and reports in Access and Excel, not pushing anything back to Oracle…W7 64 bit can utilize 32 bit drivers for the 32 bit app/database connections or so I’ve read, the .dll errors come around when setting up the ODBC. Microsoft wizards decided that the 32 bit and 64 bit drivers would have th same name!!! Just a different file location and I’m wondering if that’s the issue.