Since I have upgraded from 9.x to 10.1.0.7 of TOAD for Oracle, I have been unable to change users passwords using the TOAD UI (Alter User form). When I try to change it, I get an error that says ORA-00922: missing or invalid option.
Toad does not include double quotes around password any longer when creating the query to change the password. If you are using a symbol in your new password, this error message will be received.
Add double quotes around the password that you enter in the password box. If you enter test123!, test123! will be accepted and become the password.
Upgrade to the latest version of Toad for Oracle.
(Note: Double quotes are automatically added around the new password in v10.5. This was the latest version when this note was added. Upgrading to the latest version should fix this, unless it is changed back to not having double quotes sometime after v10.5.)
In 11g and newer, Oracle passwords by default are case sensitive. You do not need double-quotes to prevent Oracle from converting it all to uppercase. Prior to 11g, Oracle passwords are NOT case sensitive. In this case, you DO need double-quotes to prevent Oracle from converting it all to uppercase. Development does not want to automatically add quotes to the password field on the clone/copy user screen. The reason for this is that users will not be expecting it. In 10g and older, users expect their passwords to be case insensitive. If users want case-sensitive passwords in 10g and prior, the user can manually add quotes in the password box. This is consistent throughout Toad if you want a mixed case object, add double-quotes. Otherwise, Oracle will convert it to uppercase (except with 11g passwords).