ORA-12154: TNS:could not resolve the connect identifier specified (PLSQL Developer)

Question

I need to use PLSQL Developer to access oracle databases. I get the following error when I try to connect to my database. ORA-12154: TNS:could not resolve the connect identifier specified. I am able to use SQLPLUS from the command line to connect to the database, and tnsping returns successfully, but I can't figure out why PLSQL Developer will not work - it's using the right tnsnames.ora file, and the connection string in there is correct because it's the same one that tnsping uses.

Anyone have an idea what I can do to fix this? I've looked at other threads about this specific error with no luck.

tnsnames.ora

    ORCL =
      (DESCRIPTION = 
      (ADDRESS = (PROTOCOL = TCP)(HOST = MININT-AIVKVBM)(PORT = 1521)) 
      (CONNECT_DATA = 
        (SERVER = DEDICATED) 
        (SERVICE_NAME = orcl)
      )
    )

sqlnet.ora

     SQLNET.AUTHENTICATION_SERVICES= (NTS)
     NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

sqlpus command line image here

PLSQL Developer Connection Preferences: image here

Are you sure that PL/SQL Developer is using exactly the same TNS alias that you are using in SQL*Plus? My wild guess is that you're missing a domain (i.e. you need FOO.WORLD rather than FOO) — Justin Cave, Oct 10 '12 at 18:27
@JustinCave my computer doesn't have a domain name. If I run the command `echo %userdomain%` from the command line - I get my computer's hostname — ZakTaccardi, Oct 10 '12 at 18:31
I'm not talking about your computer's domain. I'm talking about the `NAMES.DEFAULT_DOMAIN` from your sqlnet.ora file, whether or not there is a domain on the TNS alias in your tnsnames.ora file (the .WORLD in FOO.WORLD), and whether or not that domain is present on the TNS alias that you are using in both SQL*Plus and PL/SQL Developer. — Justin Cave, Oct 10 '12 at 18:37
@JustinCave My `tnsnames.ora` file just contains my hostname without any domain name. My `sqlnet.ora` file does not have an entry for `NAMES.DEFAULT_DOMAIN` - do I need to give it a value? — ZakTaccardi, Oct 10 '12 at 18:48
I'm not talking about the domain on the hostname either. I'm talking about the domain (or lack thereof) on the TNS alias. Can you post your tnsnames.ora file, your sqlnet.ora file, the command line you are using for SQL*Plus, and exactly how you are configuring the connection in PL/SQL Developer? — Justin Cave, Oct 10 '12 at 18:59
@JustinCave is that what you're looking for? Thanks btw, I appreciate the help — ZakTaccardi, Oct 10 '12 at 19:15
Yes, I believe so. I can't access either image from a corporate network, though. I'll try to look at them tonight if someone else doesn't jump in. — Justin Cave, Oct 10 '12 at 19:19
here's an image of me attempting to log in to the database (I click 'OK' and I get the ORA-12154 Error http://i.stack.imgur.com/qDuJw.png — ZakTaccardi, Oct 10 '12 at 19:20
Keep in mind 32bit and 64bit clients are separate so if one is configured the other might not be. Check the help menu in PL/SQL Developer for troubleshooting information (one option lists the various Oracle Home's it can see and their contents). — Brian, Oct 10 '12 at 23:54
@Brian I'm on a 64-bit machine, but using a 32-bit installation of Oracle and 32-bit PLSQL Developer — ZakTaccardi, Oct 11 '12 at 14:04

score 33 · Accepted Answer · answered Oct 11 '12 at 14:19

33

The answer was simply moving the PLSQL Developer folder from the "Program Files (x86) into the "Program Files" folder - weird!

answered Oct 11 '12 at 14:19

ZakTaccardi

12,212
15
59
107

Thanks for letting us know! – Menefee Aug 31 '13 at 14:55
Thanks for posting this answer! I've been trying to troubleshoot this issue on a Windows 7 workstation along with the Network Engineers and the Oracle DBA at where I work. – Bryan Jul 11 '14 at 15:19
works for me too! I have spent many times to find this solution! :))) – grep Jul 28 '14 at 11:04
The parentheses caused this problem for me. After renaming the directory that housed my application to exclude the parentheses, it fixed it. – Andy Mar 19 '15 at 02:42

score 3 · Answer 2 · answered Feb 17 '13 at 16:13

This error is very common, often the very first one you get on trying to establish a connection to your database. I suggest those 6 steps to fix ORA-12154 :

Check instance name has been entered correctly in tnsnames.ora.
There should be no control characters at the end of the instance or database name.
All paranthesis around the TNS entry should be properly terminated
Domain name entry in sqlnet.ora should not be conflicting with full database name.
If problem still persists, try to re-create TNS entry in tnsnames.ora.
At last you may add new entries using the SQL*Net Easy configuration utility.

For more informations : http://turfybot.free.fr/oracle/11g/errors/ORA-12154.html

score 1 · Answer 3 · answered Sep 05 '13 at 14:34

As I can't add a comment, just thought I'd post this for completion. tufy's answer is correct, it's to do with parenthesis (brackets) in the path to the application being run.

There is an existing networking bug where the networking layer is unable to parse program locations that contain parenthesis in the path to the executable which is attempting to connect to Oracle.

Filed with Oracle, Bug 3807408 refers.

Source

score 1 · Answer 4 · answered Feb 28 '17 at 08:38

copy paste pl sql developer in program files x86 and program files both. if client is installed in other partition/drive then copy pl sql developer to that drive also. and run from pl sql developer folder instead of desktop shortcut.

ultimate solution ! chill

score 0 · Answer 5 · edited Jun 20 '20 at 09:12

0

It did not work so i switched to oracle sql developer and it worked with no problems (making connection under 1 minute).
This link gave me an idea connect to MS Access so i created a user in oracle sql developer and the tried to connect to it in Toad and it worked.

Or second solution

you can try to connect using Direct not TNS by providing host and port in the connect screen of Toad

edited Jun 20 '20 at 09:12

Community

1
1

answered Jun 24 '13 at 14:23

shareef

9,255
13
58
89

score 0 · Answer 6 · answered Jul 31 '13 at 14:44

0

For me it was bad formatting of the tnsnames.ora connect identifier. The indentation of the identifier string is required as shown in the tnsnames.ora example in the comment.

answered Jul 31 '13 at 14:44

Jan Matousek

956
3
13
15

score 0 · Answer 7 · answered Oct 28 '13 at 21:05

0

Just wanted to add -- apparently this can also be caused by installing Instant Client for 10, then realizing you want the full install and installing it again in a parallel directory. I don't know why this broke it.

answered Oct 28 '13 at 21:05

TallDave

31
2

score 0 · Answer 8 · answered Jun 19 '14 at 15:37

0

Also in addition to above solutions, also check the location where the tnsname ora file exists and compare with the path in the environment variable

answered Jun 19 '14 at 15:37

venky_ferrari

1
1

score 0 · Answer 9 · answered Nov 05 '14 at 09:58

I had an issue at work. The oracle server was "patched" and one of the databases I use could not be connect via the TNSNames entry but via Basic connection. The Database was up and admin could see it was up and running.

Addionally any application that used TNS for connecting to the database would not work either.

The problem found was that the database name was not correct in the TNS file but for some reason it's been working for years. Correcting the name fixed it for us. I did find that Oracle SQL Developer kept using the old TNS entry even after I updated it and I don't feel like reinstalling it for just one DB Connection. It appears that when the database was created it was given a smaller name then the others and via some cut and paste action within the TNSNames file it got mixed up. No one is sure how its worked as we're investigating it but the oracle patch ensured that the name had to be correct

An example of the name was it was down as "DBName.Part1.Part2" but in fact the DB name was "DBName"

score 0 · Answer 10 · answered Oct 18 '15 at 02:04

JUST copy and paste tnsnames and sqlnet files from Oracle home in PLSQL Developer Main folder. Use below Query to get oracle home

select substr(file_spec, 1, instr(file_spec, '\', -1, 2) -1) ORACLE_HOME from dba_libraries where library_name = 'DBMS_SUMADV_LIB';

sandatomo · Answer 11 · 2017-04-07T17:48:16.697

I had the same issue with a VM running CentOS7 and Oracle 11GR2 accesible from Windows 7, the solution were weird, at my local machine the tnsnames pointing to the DB had a space before the service name, I just deleted the space and then I was able to connect.

A quick example.

Wrong tnsnames.

[this is a empty space]XE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE) ) )

EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) )

Right tnsnames.

XE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE) ) )

EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) )

mr ali · Answer 12 · 2015-04-06T15:17:07.843