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!!

4 comments:

Unknown said...

Thanks for this info but I have a related problem.

What if you try with scott$test as the password ?

I could not find any other solution than to escape the $ inside the password !!

Unknown said...

It worked for me!!

Mahesh said...

Thanks, it worked well... :-)

But I am not able to change my password using alter command when my old_password has "@"

ALTER USER user_name IDENTIFIED BY new_password replace old_password;

Please suggest.

Mahesh said...

Thank you it worked well.

But I have having hard time to change the password as it has "@" and I am using below alter statement

ALTER USER user_name IDENTIFIED BY new_password replace old_password;

Please suggest.