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.
<<unexpire_password>>
declare
CONST_USERNAME constant varchar2(128) := 'PUT_USERNAME_YOU_WANT_TO_UNEXPIRE_HERE';
v_unexpire_command varchar2(4000);
begin
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.
declare
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);
begin
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')
loop
v_sql := v_sql || v_row.resource_name || ' ' || v_row.limit || ' ';
end loop;
v_sql := v_sql || 'PASSWORD_LIFE_TIME UNLIMITED';
declare
profile_already_exists exception;
pragma exception_init(profile_already_exists, -2379);
begin
execute immediate v_sql;
exception when profile_already_exists then
null;
end;
execute immediate 'alter user ' || CONST_USERNAME || ' profile ' || CONST_NEW_PROFILE_NAME;
end;
/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
