Tuesday, February 08, 2011

Oracle password with @ or Special characters


First let's see what it looks like from within sqlplus:

sqlplus /nolog
SQL> alter user scott identified by "scott@test";
User altered.

SQL> connect scott/"scott@test";
Connected.

SQL> select user from dual;
USER
------------------------------
SCOTT

SQL> connect scott/ scott@test
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
As long as the connect string was in double quotes, no problem.

Now let's try it from a linux command line:

Linux> sqlplus scott/'scott@test'
ORA-12154: TNS:could not resolve the connect identifier specified

Linux> sqlplus scott/'scott\@test'
ERROR:
ORA-01017: invalid username/password; logon denied

Linux> sqlplus scott/"scott\@test"
ERROR:
ORA-01017: invalid username/password; logon denied

Linux> sqlplus 'scott/scott\@test'
ERROR:
ORA-01017: invalid username/password; logon denied

Linux > sqlplus scott/scott\@test
ERROR:
ORA-12154: TNS:could not resolve the connect identifier speci

Nothing worked!!!
Now finally find the below syntax and works like a charm!
sqlplus scott/\”scott@test\”@TEST_DB
Though it worked, but I am not going to use ‘@’ anymore with oracle users.
Lessons learned!!

No comments: