How to resolve ORA-28002: the password will expire

We often come across the error ORA-28002 the password will expire in day to day activities. This can happened with developers and DBA both.

ORA-28002: the password will expire within 7 days
Cause: The user’s account is about to about to expire and the password needs
to be changed.
Action: Change the password or contact the database administrator.

Reasons

This happen when you have not change password in Password_life_time value of the profile set into your account and you have entered into PASSWORD_GRACE_TIME value

select username , profile from dba_users where username='TEST';
username    profile
------      -----
SCOTT      TECH_PW

SQL> select RESOURCE_NAME,resource_type,LIMIT from dba_profiles where PROFILE='TECH_PW' ;
RESOURCE_NAME RESOURCE LIMIT
----------    --------  -----
COMPOSITE_LIMIT KERNEL DEFAULT
SESSIONS_PER_USER KERNEL DEFAULT
CPU_PER_SESSION KERNEL DEFAULT
CPU_PER_CALL KERNEL DEFAULT
LOGICAL_READS_PER_SESSION KERNEL DEFAULT
LOGICAL_READS_PER_CALL KERNEL DEFAULT
IDLE_TIME KERNEL DEFAULT
CONNECT_TIME KERNEL DEFAULT
PRIVATE_SGA KERNEL DEFAULT
FAILED_LOGIN_ATTEMPTS PASSWORD 5
PASSWORD_LIFE_TIME PASSWORD 90
PASSWORD_REUSE_TIME PASSWORD UNLIMITED
PASSWORD_REUSE_MAX PASSWORD 10
PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
PASSWORD_LOCK_TIME PASSWORD DEFAULT
PASSWORD_GRACE_TIME PASSWORD DEFAULT

Default values can be found using below query

select RESOURCE_NAME,resource_type,LIMIT from dba_profiles where PROFILE='DEFAULT';
RESOURCE_NAME RESOURCE LIMIT
----------    -------  -----
COMPOSITE_LIMIT KERNEL UNLIMITED
SESSIONS_PER_USER KERNEL UNLIMITED
CPU_PER_SESSION KERNEL UNLIMITED
CPU_PER_CALL KERNEL UNLIMITED
LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
LOGICAL_READS_PER_CALL KERNEL UNLIMITED
IDLE_TIME KERNEL UNLIMITED
CONNECT_TIME KERNEL UNLIMITED
PRIVATE_SGA KERNEL UNLIMITED
FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
PASSWORD_LIFE_TIME PASSWORD UNLIMITED
PASSWORD_REUSE_TIME PASSWORD UNLIMITED
PASSWORD_REUSE_MAX PASSWORD UNLIMITED
PASSWORD_VERIFY_FUNCTION PASSWORD NULL
PASSWORD_LOCK_TIME PASSWORD UNLIMITED
PASSWORD_GRACE_TIME PASSWORD 7

So we have 90 days password expiration with grace period of 7 days

Solutions

1.Change the password and this problem will go away

sqlplus test/test
SQL*Plus: Release 11.2.0.2.0 Production on Wed Apr 20 16:16:05 2020
Copyright (c) 1982, 2009, Oracle. All rights reserved.
ERROR:
ORA-28002: the password will expire within 7 days
Connected to:
Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
SQL> PASSWORD
Changing password for TEST
Old password:
New password:
Retype new password:
Password changed

Now you will be all set for the period of 90 days and you need to change password before 90 days

2. You can altogether eliminate this error for forever by changing the profile to set PASSWORD_LIFE_TIME as unlimited

You can create either new profile for this user with this specific setting or change the profile assigned only to make the changes

New Profile

SQL> CREATE PROFILE TEST_TMP LIMIT
PASSWORD_LIFE_TIME UNLIMITED
;

SQL> select RESOURCE_NAME,resource_type,LIMIT from dba_profiles where PROFILE='TEST_TMP' ;
RESOURCE_NAME RESOURCE LIMIT
-----------   -------  -----
COMPOSITE_LIMIT KERNEL DEFAULT
SESSIONS_PER_USER KERNEL DEFAULT
CPU_PER_SESSION KERNEL DEFAULT
CPU_PER_CALL KERNEL DEFAULT
LOGICAL_READS_PER_SESSION KERNEL DEFAULT
LOGICAL_READS_PER_CALL KERNEL DEFAULT
IDLE_TIME KERNEL DEFAULT
CONNECT_TIME KERNEL DEFAULT
PRIVATE_SGA KERNEL DEFAULT
FAILED_LOGIN_ATTEMPTS PASSWORD 5
PASSWORD_LIFE_TIME PASSWORD UNLIMITED
PASSWORD_REUSE_TIME PASSWORD UNLIMITED
PASSWORD_REUSE_MAX PASSWORD 10
PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
PASSWORD_LOCK_TIME PASSWORD DEFAULT
PASSWORD_GRACE_TIME PASSWORD DEFAULT

alter user TEST profile TEST_TMP;

Now you can check the status of account and then change the password

SQL> SELECT ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME='TEST'; ACCOUNT_STATUS 
--------------
EXPIRED(GRACE)

sqlplus test/test
SQL*Plus: Release 11.2.0.2.0 Production on Wed Apr 20 16:16:05 2020
Copyright (c) 1982, 2009, Oracle. All rights reserved.
ERROR:
ORA-28002: the password will expire within 7 days
Connected to:
Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
SQL> PASSWORD
Changing password for TEST
Old password:
New password:
Retype new password:
Password changed

Or

Alter user test identified by <new pass>;

Change the existing Profile

select username , profile from dba_users where username='TEST';
username profile
------ -----
SCOTT TECH_PW

SELECT LIMIT FROM DBA_PROFILES WHERE PROFILE='TECH_PW' AND RESOURCE_NAME='PASSWORD_LIFE_TIME';
LIMIT
----
90

ALTER PROFILE TECH_PW LIMIT PASSWORD_LIFE_TIME UNLIMITED;
Profile altered.

SELECT LIMIT FROM DBA_PROFILES WHERE PROFILE='TECH_PW' AND RESOURCE_NAME='PASSWORD_LIFE_TIME';
LIMIT
------
unlimited

Now change the password

sqlplus test/test
SQL*Plus: Release 11.2.0.2.0 Production on Wed Apr 20 16:16:05 2020
Copyright (c) 1982, 2009, Oracle. All rights reserved.
ERROR:
ORA-28002: the password will expire within 7 days
Connected to:
Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
SQL> PASSWORD
Changing password for TEST
Old password:
New password:
Retype new password:
Password changed

Or

Alter user test identified by <new pass>;

Hope you like post on the How to resolve ORA-28002: the password will expire. Please do provide the feedback and let me know if any doubts

Related Articles

ORA-01017: invalid username/password; logon denied
alter user identified by values
Create User in Oracle
ora-00904 invalid identifier
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_4003.htm
ORA-28000 the account is locked
ORA-03113
ORA-29285: file write error

Reader Interactions

Leave a Reply Cancel reply