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
This question is related to
oracle
plsql
sqlplus
plsqldeveloper
tnsnames
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.
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 :
For more informations : http://turfybot.free.fr/oracle/11g/errors/ORA-12154.html
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
Or second solution
you can try to connect using Direct not TNS by providing host and port in the connect screen of Toad
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.
Answer: Go to the folder where you have installed ORACLE DATABASE. such as E:\oracle\product\10.2.0 [or as your oracle version]\db-1\network\ADMIN
and then copy the two files (1) sqlnet.ora, (2) tnsnames.ora and close this folder.
Go to the folder where you have installed ORACLE DEVELOPER. such as E:\DevSuitHome_1\NETWORK\ADMIN
then rename the two files (1) sqlnet.ora, (2) tnsnames.ora and paste the two copied files here and Your are OK.
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.
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"
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
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';
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) ) )
Source: Stackoverflow.com