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

4 comments:

  1. UnknownJuly 21, 2015 at 4:34 AM

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

    ReplyDelete
  2. UnknownSeptember 29, 2016 at 3:47 AM

    It worked for me!!

    ReplyDelete
  3. MaheshNovember 5, 2016 at 1:38 AM

    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.

    ReplyDelete
  4. MaheshNovember 5, 2016 at 1:39 AM

    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.

    ReplyDelete