Symptom
Upon login, you are receiving messages, “ORA-28002: The Password will Expire within x Days”.
Solution
1. Find the account status and profile usage on the affected username.
SQL> select USERNAME, ACCOUNT_STATUS, PROFILE from dba_users where USERNAME='AYAMAS'; USERNAME ACCOUNT_STATUS PROFILE -------- -------------- ------- AYAMAS EXPIRED(GRACE) DEFAULT
2. From the profile, find the value set for PASSWORD_LIFE_TIME & PASSWORD_GRACE_TIME.
SQL> select RESOURCE_NAME, LIMIT from dba_profiles where PROFILE='DEFAULT'; RESOURCE_NAME LIMIT -------------------------------- ---------------------------------------- COMPOSITE_LIMIT UNLIMITED SESSIONS_PER_USER UNLIMITED CPU_PER_SESSION UNLIMITED CPU_PER_CALL UNLIMITED LOGICAL_READS_PER_SESSION UNLIMITED LOGICAL_READS_PER_CALL UNLIMITED IDLE_TIME UNLIMITED CONNECT_TIME UNLIMITED PRIVATE_SGA UNLIMITED FAILED_LOGIN_ATTEMPTS UNLIMITED PASSWORD_LIFE_TIME UNLIMITED RESOURCE_NAME LIMIT -------------------------------- ---------------------------------------- PASSWORD_REUSE_TIME UNLIMITED PASSWORD_REUSE_MAX UNLIMITED PASSWORD_VERIFY_FUNCTION NULL PASSWORD_LOCK_TIME UNLIMITED PASSWORD_GRACE_TIME UNLIMITED
3. Set the PASSWORD_LIFE_TIME & PASSWORD_GRACE_TIME to unlimited or any longer value.
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_GRACE_TIME UNLIMITED;
4. Next, alter the user password by below command. Change the user password to a new or old password. Check the account status again.
SQL> alter user AYAMAS identified by <PASSWORD>;
