Home > Oracle > Fix unknown expired Oracle passwords without changing them

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!

Advertisements
Categories: Oracle
  1. Boost
    2013-12-16 at 03:22

    Great!! Thank you

  2. MM
    2014-05-02 at 20:09

    This is great. Thanks a lot

  3. dekta
    2014-09-14 at 05:20

    life saver!! my OBIEE RCU-created users were expired under oracle11g. this article worked like a charm!!

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: