This is a follow up to my earlier post about SQLDeveloper being moody and suddenly refusing to connect to a database via TNS.
Having had a bit of a dig around, it would seem that this problem is not platform specific and affects Windows in the same way.
At this point, I’d like to say a big “thank you” to Grzegorz Wilczura, who referred me to this article by Sue Harper.
If you’re hitting this problem on Windows, then you may want to follow the instructions there to set up a TNS_ADMIN environment variable.
Remember, this problem had two primary symptoms :-
- Empty Network Alias list when defining a TNS connection
- When testing an existing TNS connection you get :
Status: Failure – Test failed : no ocijdbc11 in java.library.path
In Sue’s article, it states that SQLDeveloper looks for a tnsnames.ora in the following places in this order :
- $HOME/.tnsnames.ora
- $TNS_ADMIN/tnsnames.ora
- /etc/tnsnames.ora
- $ORACLE_HOME/network/admin/tnsnames.ora
Only one of these places is has an absolute path. The rest all rely on environment variables being set. However, when I run SQLDeveloper from the Ubuntu desktop menu, I’m not starting a shell, so my .bashrc doesn’t get executed. Therefore, these variables are not set.
When I setup my first tns connection, I’d just installed sqldeveloper and ran it by executing the shell script ( sqldeveloper.sh) from a Terminal Window. Of course, the $ORACLE_HOME was set in this environment and SQLDeveloper could therefore see the tnsnames.ora in $ORACLE_HOME/network/admin.
All of this means that the cause of the problem is that SQLDeveloper cannot see, or can no longer see, the tnsnames.ora file.
Copying the tnsnames.ora to /etc will fix the problem. However, probably the best solution is to ensure that we’re only referencing one tnsnames.ora and don’t replicate it. That way, we only ever have to change it in one place, should the need arise.
So, the alternative I’ve chosen is to set the $ORACLE_HOME environment variable in sqldeveloper.sh – the script that gets called to start SQLDeveloper.
Start a terminal and go to the SQLDeveloper home directory ( in my case, I installed SQLDeveloper in /opt) :-
cd /opt/sqldeveloper sudo gedit sqldeveloper.sh
Now amend the file so it looks something like this :
#!/bin/bash ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server export ORACLE_HOME cd "`dirname $0`"/sqldeveloper/bin && bash sqldeveloper $*
Now re-start SQLDeveloper from the Ubuntu menu. Remember, this menu item is simply executing the shell script we’ve just changed.
If you have an existing tns connection defined then you can test doing the following :
- Right-click the connection and select Properties from the pop-up menu.
- This will bring up the New/Select Database Connection window.
- Enter the password in the Password field
- Hit the test button.
The test should now succeed.
If you haven’t got a TNS connection defined currently, you should now be able to test by setting one up, with no problem.
Hi Mike – I do not understand what the content of Sue’s article has to do with the missing ocijdbc11, or even what your article has to do with it 😦 I only uderstand that you can’t connect because your tnsnames.ora was missing, but what is the relation to ocijdbc11 here?
LikeLike
Werner,
the reason that SQLDeveloper gives the missing ocijdbc11 error is that it cannot see the tnsnames.ora.
The tnsnames.ora is not missing. It’s right where it’s supposed to be ( usually $ORACLE_HOME/network/admin)
The reason that SQLDeveloper can’t see it is because looking for it using environment variables ( $HOME, $TNS_ADMIN, $ORACLE_HOME) which have not been set for the session in which it’s running.
By setting these environment variables, you allow SQLDeveloper to find the tnsnames.ora. This means that it will now allow you to connect via TNS and will no longer give you the ocijdbc11 error.
HTH
Mike
LikeLike
Hey, Mike —
I was able to get SQLDeveloper to connect using your guidance regarding setting an ORACLE_HOME to help it find the tnsnames.ora. Using this tool on a Windows box I simply set the environment variable from ‘Advanced’ tab on the system properties. Making sure the SQLDeveloper check box for using OCI/Thick client connections was unchecked, I was able to once again connect.
Thanks for the heads up.
Peace.
LikeLike
Hi Bruce,
I am still kind of lost with your windows box solution. Which server veriable are you referring to?
Thanks
Hardy
LikeLike
Hello, Hardy —
I was referring to setting an ORACLE_HOME environment variable from my windows box using the Properties/Advanced/Environment Variables tab. You can also arrive at a compatible solution by following rockspider’s advice in setting the tnsnames directory preference within the SQLDeveloper tool. Both approaches appear to work.
HTHYO. Peace.
LikeLike
OK – possibly weirder…
This is for SQLDeveloper on Linux.
I *did* have TNS_ADMIN set correctly, but that directory also contained a file called tnsname.ora.test, to which I had no access. SQLDeveloper tried to read it (even though it had already read the tnsnames.ora file which was there and which it could read). Once it found it couldn’t read it it seems to have ignored TNS, so I got the “no ocijdbc11” error.
Why is it looking at tnsname.ora.test in the first place?
And why does it ignore data from the correct file?
This strange lookup behaviour is not exhibited by an older linux binary install (of Oracle9) – it just looks for tnsnames.ora.
Also, SQLDeveloper makes no attempt to look for $HOME/.tnsnames.ora
LikeLike
Thank you !
I also set the ORACLE_HOME as Bruce described, and it worked.
LikeLike
Just use the setting in sqldeveloper:
Tools > Preferences > Database > Advanced > Tnsnames Directory
Click Browse to select the location of the parent folder of your tnsnames file.
LikeLike
Thank u thank u thank YOU!
LikeLike
Thank you, being a noob this helped me a lot.
LikeLike
EXCELLENT – worked for me. Thank You so much!
LikeLike
Yes !! Thank you !
LikeLike
Great. Thank you so much!
LikeLike
You rock rockspider!! I’ve been searching everywhere for the solution.
Mike,
You should move rockspider’s solution to the top of your article.
I appreciate you guys taking the time to post.
-Duke
LikeLike
Duke,
Rockspider’s solution is perfect for SQLDeveloper. The reason I chose to go down the route of setting system wide variables is because, if you have any other Oracle client software on your system, that will also need to see the tnsnames.ora to connect.
Notwithstanding that, I’m really glad you found a solution here. Thanks again to Rockspider for the info.
Mike
LikeLike
For Windows, Just select Advanced tab instead of TNS and type the complete JDBC url. It worked for me
LikeLike
Thanks Satish
That worked for me on my Mac. Big help.
Greg
LikeLike
I have such error on Windows. Dissapear after adding to user environment variables two values, In my case:
ORACLE_HOME set to C:\oracle
TNS_ADMIN set to C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN
LikeLike
Thanks for your solution. I got the same issue, and tried all kinds of ways to fix it. Only modifing the two variables as you said fixed it.
LikeLike
None of these solutions are working for me 😦
LikeLike
Diya,
what version of SQLDeveloper are you using and which OS ?
Also, what environment variables do you have set ?
Mike
LikeLike
I was facing the same problem with ocijdbc11. Tried many things with Environment Variables and Registry with no effect. Then started using version 3.1, could use it without any problem. Now I have both version 2.1 that is having problem and not usable and also version 3.1 that is working perfectly.
LikeLike
Specify the TNS entry folder under the Tools–>Preferences–>Database–>Advanced tab and also add the jdbc driver path under the Tools–>Preferences–>Third Party Jdbc Driver. That all works fine to me.
Cheers,
Suresh
LikeLike
I don’t see an option for “Third Party Jdbc Driver”? Would you mind helping me?
LikeLike
im installing the sql developer but it shows some errors like
# An unexpected error has been detected by HotSpot Virtual Machine:
EXCEPTION_ACCESS_VIOLATION (0xc0000005) at pc=0x77c47fd4, pid=308, tid=3696
LikeLike
This is an example of a truly bizarre error message. I had a file called tnsnames.old in the folder that was pointed to by the Tnsnames directory variable in tools/preferences/databases/advanced .When I removed that the error went away. So even though it appears that the problem is with a java path value, that is just to throw you off the scent!
LikeLike
Thanks dude. you’ve made my day.. I was having the same error with my mac.
i have modified my sqldeveloper.sh in /Applications/SQLDeveloper.app/Contents/MacOS to include TNS_ADMIN i previously defined in my .bash_profile.
Thanks again.
LikeLike
Dear All,
If you don’t have Oracle Software on your system even though you can connect to the database :
Please follow below steps:
Connection name: ABDD
USERNAME: ….
PASSWORD: …..
Connection Type : BASIC Role : Default
Host name : IP of Oracle Server
PORT: 1521
SID: DB Name
Regards,
Ashish
LikeLike