What This Error Means

You’re trying to login to an Oracle database and you get ORA-28001: the password has expired. Yikes! This means that the user’s profile forces them to periodically change their password, due to a security precaution. So, if you’re getting this error, it means a password change is overdue. Oracle is trying to force you to change your password to something different so that your database remains secure.

There are times when this feature is an annoyance, because sometimes changing your password is more trouble than it’s worth. So, here’s a workaround that will let you unexpire your password without having to change it! Or, even know what the password is!!

How to Fix The Issue Now

In the below block of code, you’ll want to replace PUT_USERNAME_YOU_WANT_TO_UNEXPIRE_HERE with whomever you want to unexpire the password for.

  v_unexpire_command varchar2(4000);

  execute immediate ' alter user ' || CONST_USERNAME || ' account unlock';

  select 'alter user ' || name || q'< identified by values '>' || spare4 || ';' || password || q'<'>' into v_unexpire_command
  from sys.user$ 
  where name = unexpire_password.const_username;

  execute immediate v_unexpire_command;

end unexpire_password;

Run the block of code, and your account will be unlocked and the password unexpired.

How to Prevent “ORA-28001: the password has expired” From Happening In The Future

You say you want to prevent your password from expiring in the future? Not problem, because all you need is a profile that prevents the password from expiring.

The safest way to do this is to create a new profile that has all the same settings as the old profile, but with one change. The new profile should have the PASSWORD_LIFE_TIME set to UNLIMITED.

Below is a script that can help you create the new profile. Just replace USERNAME_GOES_HERE with whatever username you wish to have no password expiration. Optionally, you can change the name of the profile that you’re creating. In the code snippet below, I called it NO_PASSWORD_EXPIRE, but you can change the script so that it’s more meaningful to you.

  CONST_USERNAME constant varchar2(128) := 'USERNAME_GOES_HERE';
  CONST_NEW_PROFILE_NAME constant varchar2(128) := 'NO_PASSWORD_EXPIRE';
  v_existing_profile varchar2(128);
  v_sql varchar2(32767);
  select du.profile into v_existing_profile
  from dba_users du
  where du.username = CONST_USERNAME;
  v_sql := ' create profile ' || CONST_NEW_PROFILE_NAME || ' limit ';
  for v_row in (select dp.resource_name, dp.limit
                from dba_profiles dp
                where dp.profile = v_existing_profile
                  and dp.resource_name <> 'PASSWORD_LIFE_TIME')
    v_sql := v_sql || v_row.resource_name || ' ' || v_row.limit || ' ';
  end loop;

  v_sql := v_sql || 'PASSWORD_LIFE_TIME UNLIMITED';
    profile_already_exists exception;
    pragma exception_init(profile_already_exists, -2379);
    execute immediate v_sql;
  exception when profile_already_exists then 
  execute immediate 'alter user ' || CONST_USERNAME || ' profile ' || CONST_NEW_PROFILE_NAME;

Free Oracle SQL Tuning Guide

Check out my FREE guide! 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!

Get it here: tuningsql.com/secrets

7 SQL Tuning Secrets You can Use Immediately, Even If You've Never Tuned A Query In Your Life

Leave a Reply

Your email address will not be published. Required fields are marked *