ORA-28001: the password has expired - Permanent Fix for NOEXPIRY for Oracle User

advertisements

_____________________________________________________________________________________________________________________

Error Description: ORA-28001: the password has expired message is getting while connect to the user.

SQL> CONNECT TEST/TEST;

ERROR:

ORA-28001: the password has expired

Changing password for TEST

New password:

Retype new password:

Password changed

Connected.

You have to check the 2 parameters(FAILED_LOGIN_ATTEMPTS, PASSWORD_LIFE_TIME) from the dba_profiles for the corresponding profile of the user. Reset the values for these parameters to UNLIMITED to fix the user to the noexpire mode.

1. Check the profile for the user TEST.

SQL> select profile from dba_USERS where username ='TEST';

PROFILE

------------------------------

NOEXPIRY

2. Find out the values for the resource values from dba_profiles and set it to unlimited.

SQL> SELECT RESOURCE_NAME ,LIMIT FROM DBA_PROFILES  WHERE PROFILE='NOEXPIRY' and RESOURCE_NAME in ('FAILED_LOGIN_ATTEMPTS','PASSWORD_LIFE_TIME');

RESOURCE_NAME                    LIMIT

-------------------------------- ----------------------------------------

FAILED_LOGIN_ATTEMPTS            1

PASSWORD_LIFE_TIME               120

SQL> alter profile NOEXPIRY limit FAILED_LOGIN_ATTEMPTS unlimited;

Profile altered.

SQL> alter profile NOEXPIRY limit PASSWORD_LIFE_TIME unlimited;

Profile altered.

3. Now you connect to the user(TEST) and reset the password and it will be set permanently 

See: Reset the Password to Existing one for Expired / Locked Users

_____________________________________________________________________________________________________________________

Website Stats