GG

As we have always been encouraged to define complex passwords everywhere, it is likely that you feel the need to set passwords with special characters in an Oracle database too.
But should you ?

Let’s make some tests.

Source code

CREATE user testuser IDENTIFIED BY 33!%@;

 
What we get is this :
ORA-00988: missing or invalid password(s)

Well, let’s look at the rules that Oracle want us to follow while defining a password then :

o Passwords must be from 1 to 30 characters long.
o Passwords cannot contain quotation marks.
o Passwords are not case sensitive.
o A Password must begin with an alphabetic character.
o Passwords can contain only alphanumeric characters and the
  underscore (_), dollar sign ($), and pound sign (#). Oracle
  strongly discourages you from using $ and #..
o A Password cannot be an Oracle reserved word (eg: SELECT).

According to that text, we should not use special characters in passwords. So facing an error like the one above is not unexpected.
It is clear that you should NOT use those characters, but this does not mean you CAN’T..

Let’s do some more useless & time wasting practices then :

(Note that we are using @ char on purpose which is i think the worst char for an oracle password

Source code

CREATE user testuser IDENTIFIED BY "33!%@";

User successfully created..
Nice. So we can provide passwords with special characters by using “ (Double quotation marks).

But can we use this passwords afterward ?

Source code

sqlplus testuser/33!%@@testdb

 
ORA-12154: TNS:could not resolve the connect identifier specified

I guess not.
So let’s try it with double quotation marks again.

Source code

sqlplus testuser/"33!%@"@testdb

ORA-12154: TNS:could not resolve the connect identifier specified

No luck again..

If you kept reading from the beginning, i am expecting you to decide NOT TO USE special characters already.

*Sigh*

Okey, let’s make our final move then:

Source code

sqlplus testuser/\"33!%@\"@testdb

Success.. (with the help of double quotation marks and escape characters)

As you see, to be able to use this password we needed some tricks which obviously a solid reason itself to avoid using special characters ..

-Guven