Replace clause for changing one’s password.

Starting with Oracle 11g, if you get the following error when changing your own password, it means that you have to include REPLACE clause in your ALTER USER command:

ALTER USER testuser IDENTIFIED BY testpassword;

Oracle: “ORA-28221: REPLACE not specified” 

Syntax with REPLACE clause:

ALTER USER testuser IDENTIFIED BY testpassword REPLACE oldpassword;

User altered.

If your password contains special characters, you may need to put it in quotations:

ALTER USER testuser IDENTIFIED BY "testp@ssw@rd" REPLACE oldpassword;

User altered.

As per Oracle documentation,

You can omit the REPLACE clause if you are setting your own password or you have the ALTER USER system privilege and you are changing another user’s password. However, unless you have the ALTER USER system privilege, you must always specify the REPLACE clause if a password complexity verification function has been enabled, either by running the UTLPWDMG.SQL script or by specifying such a function in the PASSWORD_VERIFY_FUNCTION parameter of a profile that has been assigned to the user.

Source: http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_4003.htm#SQLRF01103

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s