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
Leave a Reply