How to fix “ORA-28002: the password will expire within X days” errors, by Zahar Hilkevich

In Oracle 11g (and probably all other subsequent releases) the DEFAULT profile comes with PASSWORD_LIFE_TIME attribute equal to 180 days. So in about 6 month users will start getting ORA-28002 errors if they have not changed their passwords since their accounts were created.

If it is critical not to expire passwords automatically (which may or may not be a good idea), a DBA should alter the default profile as follows:

ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME UNLIMITED
/

This change will not apply immediately to those users who already started getting ORA-28002 errors. All they need to do is to change their password even to the same value.
At the same time, a DBA can help the users and “alter” their passwords even without knowing them. This becomes really helpful when the number of such database users is high.

When I first faced this issue I came up with the following script that does the trick. The script should be executed by a sysdba user.

DECLARE
  CURSOR c IS
   SELECT u.name, u.spare4
   FROM user$ u JOIN dba_users d ON u.name=d.username
   WHERE d.account_status='EXPIRED(GRACE)';
BEGIN
   FOR v IN c LOOP
	EXECUTE IMMEDIATE 'ALTER USER ' || v.name || ' IDENTIFIED BY VALUES ''' || v.spare4 || '''';
   END LOOP;
END;
/

Here you go!

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.