1

I'm trying to connect to from SQL*Plus and get error ORA-12154, but I can connect from SQL Developer without any error. I can tnsping and also lsnrctl is okay and the listener is up and running.

I can connect as sys like sqlplus sys as sysdba which works fine; but with another user I do sqlplus username and when I type the password it gives me:

ORA-12154: TNS:could not resolve the connect identifier specified
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
hi4ppl
  • 595
  • 1
  • 6
  • 21
  • Can you please post the entire error message you are getting? – gvenzl Sep 01 '14 at 10:42
  • Update your question with information how do you connect with sqlplus. – yamny Sep 01 '14 at 10:46
  • Hi thanks for replay... added additional info – hi4ppl Sep 01 '14 at 11:08
  • You will most likely have to specify the TNS connection you want to connect to: sqlplus @ – gvenzl Sep 01 '14 at 11:12
  • 2
    Does your password have an `@` symbol in it? – Alex Poole Sep 01 '14 at 11:14
  • Hi yes my password have that symbol in it... is that the problem? ... thanks alex that work... I changed the password and removed @ sign and all is well now thanks for the time :D – hi4ppl Sep 01 '14 at 11:20
  • I had the same problem when running in Ubuntu but my issue wasn't password related. Turned out that my problem was with setting in the SQLNET.ORA file. The NAMES.DEFAULT_DOMAIN = world and NAMES.DIRECTORY_PATH = (TNSNAMES,ONAMES) were causing the issue. I removed these and it all worked OK. – Mark Davies Feb 12 '16 at 12:09

2 Answers2

7

SQL*Plus interprets an @ symbol as the start of a TNS alias. If your password has that in it then it will be interpreted that way even if you are not trying to connect over SQL*Net, but with a local connection. So if you do:

sqlplus user/p@ssword

... then it will take just the p as the password, and the @ssword as the alias, so it will try to find a TNS alias of ssword in your tnsnames.ora file. Since that doesn't exist, you'll get the "ORA-12154: TNS:could not resolve the connect identifier specified" error.

That applies when you put your password in at the prompt too, so if you do:

sqlplus user
Enter password: p@ssword

you'll get the same thing. You can connect by enclosing the password in double-quotes:

sqlplus user
Enter password: "p@ssword"

but you can't do that with the unprompted version.

You're probably better off changing your password to something without that particular character if you want to be able to connect from SQL*Plus as well as from other clients that don't interpret TNS in the same way.

SQL Developer, for example, accepts the password and connection settings as separate fields; it isn't trying to parse the string you entered to identify its components, so it doesn't get confused. Even if you tell SQL Developer to connect using a TNS alias, it will use the tnsnames.ora value you select from its drop-down list, it won't try to take it from the user or password fields.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
0

This one worked for me ( and was easier to implement from my behalf )

From : TNSPING OK but sqlplus gives ORA-12154?

Create an environment variable TNS_ADMIN that points to the directory where your tnsnames.ora file resides. Then try to connect with sqlplus.

TNS_ADMIN = D:\oracle\12.1.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora

If that works, then my guess is you maybe installed the Oracle client software too, and when you run sqlplus, it looks for the tnsnames.ora file in your client home.

-- Instructions for Adding the Environment variable TNS_ADMIN in windows

  1. Go to control panel / system
  2. select Advanced system settings
  3. Select "Advanced" tab, and the environment variable button is at the bottom.
  4. create new variable TNS_ADMIN and give the path where the .ora files are stored. e.g. C:\app\oracle\product\11.2.0\client_1\network\admin
Etay Gudai 972-522-3322-47
  • 599
  • 8
  • 20