We often come across the error ORA-28002 the password will expire in day-to-day activities. This can happen 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 happens when you have not changed the password in the 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 a 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 90 days and you need to change your password before 90 days
2. You can altogether eliminate this error forever by changing the profile to set PASSWORD_LIFE_TIME as unlimited
You can create either a 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 the 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 the post on the How to Resolve ORA-28002: the password will expire. Please do provide 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 in oracle
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
Password Version in oracle
ORA-28007: check out how to resolve ORA-28007 in oracle, how to reset the same password in oracle,PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX
