Fix unknown expired Oracle passwords without changing them

The way Oracle handles password expiration can be quite frustrating at times. To enable an expired user the DBA must basically set the password. Locked users can be unlocked with a simple command, but there is no way to unexpire an expired password. If the password expired by mistake and is used by a system rather than a user, changing it may be a big deal. You need to get things up and running and you need to do it fast. So, what to do?

Fortunately it is possible to set the password to the same value it had before using identified by values and the hashed password. Oracle does not show the hashed password in dba_users, but it can be retrieved.

This code finds specified users, unlocks their accounts and resets the passwords:

begin
  for rec in (select
    regexp_substr( dbms_metadata.get_ddl('USER', username), '''[^'']+''') pw,
    username from dba_users where username IN ('SYSMAN')) loop
    execute immediate 'alter user ' || rec.username || ' account unlock';
    execute immediate 'alter user ' || rec.username || 
      ' identified by values ' || rec.pw;
  end loop;
end;
/

Simply replace the user(s) in the query (SYSMAN in this case) and run the script as a DBA. Business as usual again!