Oracle Database Discussions

Hi All,

I don't know if this has ever been posted, but since I've spent a week figuring it out (no joke, a week), I thought I would post it so that no one else has to waste this much time.

"ORA-12154: TNS:could not resolve the connect identifier specified" from what I've seen on this site is a major problem for a lot of people, but I did not find a reference to my specfic problem.

FYI--
My Database is called "ordidm"
userID: SYSMAN
password: p@ssw0rd

My specific issue was the following:


I can....

- Connect to the database via the client "admin assstant" GUI in Windows while supplying the userID and password using the "Database Authentication".
- Connect via sqlplus as "/as sysdba"
- "tnsping oraidm" passes works just fine.

I CANNOT....

- connect to oraidm at the dos prompt by using sqlplus, regardless of whether or not I supply all the user authentiocation information and db name on one line, or...if I just type "sqlplus" at the command prompt and then enter the userID and password when prompted. Every time I get the "ORA-12154: TNS:could not resolve the connect identifier specified" error message.

Ready for this?? (some may have guessed this already) The problem is the "@" in my password. After looking closely at the trace logs, I noticed that one of the entries was...


---------------------------------------------------------------------------------------------
[09-MAR-2009 08:23:12:921] nnftrne: Original name: ssw0rd
---------------------------------------------------------------------------------------------

Remember that the format for connecting on one line at the dos prompt is

C:/> sqlplus SYSMAN/p@ssw0rd oraidm

or you can do the following...


C:\Documents and Settings\Administrator.WIN2K3>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 9 09:08:50 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter user-name: scott
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>

(BTW-I just started using the "scott" ID because from what I understand, changing the SYSMAN userID password is kind of a pain. Figured just using "scott" ID would be easier to test)

So of course what it's doing is counting everything after the first "@" as the database name...which of course it can't find in the tnsnames.ora file. Also, contrary to some other posts I found here and there, enclosing the userID and/or passowrd in quotes does not do any good. Apparently, even when using the 2nd method...in the background, the end result is it must pass the whole thing as one long string much like in the 1st method (as you will get the same truncated "Original Name" of "ssw0rd" in the log)

So...the question is, why does Oracle even allow "@" in the password if it will become an issue with sqlplus??? Also, if you can't (or shouldn't) use it, why isn't it documented somewhere? At the least I would expect the installer to at least warn you ahead of time that an "@" is not suggested. I don't remember it being there. Maybe it's just me and I'm just missing this info somewhere

I sincerely hope this saves someone else a week of their life. ;-)

-Eric