ORA-28002 : the password will expire within 7 days

The default user policy is that password expires in 180 days.  We can check the settings with below statement:

SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';

It is not suitable for application user.  We need to disable the default password expiry policy:

1. Confirm the profile name which the concerned user uses:
   sql>select username,account_status,expiry_date,profile from dba_users where username='USERNAME';
   
   USERNAME                       ACCOUNT_STATUS                   EXPIRY_DA
   ------------------------------ -------------------------------- ---------
   PROFILE
   ------------------------------
   username                        EXPIRED                          10-MAR-14
   DEFAULT
   
   
2. as the password life time is set from PROFILE, we check the 'DEFAULT' profile current setting and it has been already set to unlimited.
   
   SQL> select * from dba_profiles s where s.profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';
   PROFILE                        RESOURCE_NAME                    RESOURCE
   ------------------------------ -------------------------------- --------
   LIMIT
   ----------------------------------------
   DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD
   UNLIMITED
   
   
3. If the password life time is not unlimited, change the password expiry date to unlimited:
   sql>ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
4. For the expired username/password, the above change will not be effective.  We need to update the password again (can use the same password).  There will be no more error next time:
   $sqlplus / as sysdbaSQL> alter user username identified by XXXXX; ß (If the password string contain special character, use “ ” to quote the whole string)
5. Verify the user status from dba_users to make sure the user status is open and there is no expiry date:
   
   
   SQL> select username,account_status, expiry_date,profile from dba_users where username='USERNAME';
   
   
   
   
   USERNAME                       ACCOUNT_STATUS                   EXPIRY_DA
   ------------------------------ -------------------------------- ---------
   PROFILE
   ------------------------------
   username                        OPEN
   DEFAULT