ORA-28002: the password will expire within string days

Hi,

Sometimes You can get “ORA-28002: the password will expire within string days ” error.

Details of error are as follows.

ORA-28002: the password will expire within string days

Check the users which users are expired.

SQL> SELECT username, account_status FROM dba_users WHERE ACCOUNT_STATUS LIKE '%EXPIRED%';

You can solve this error by using its existing password or with new password as follows.

ALTER USER USERNAME IDENTIFIED BY PASSWORD;



SQL> ALTER USER MEHMET IDENTIFIED BY DEVECI;         
User altered.

Find the related profile and change profile PASSWORD_LIFE_TIME to UNLIMITED as follows.

select * from dba_profiles where RESOURCE_NAME LIKE ‘PASSWORD_LIFE_TIME’;

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
Profile altered.

SQL>

You can recover and fix all users in the database that can get the same error using and executing the following script.

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

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/