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


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
  1. 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.

  1. Now you connect to the user(TEST) and reset the password and it will be set permanently 
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google