ORA-28002: the password will expire within 7 days error occurs when the Oracle user password is about to expire and the password needs to be changed. If the user profile is set to change the password on a regular basis, you will receive this error message when your account password reaches its expiry date. To avoid receiving this password expiry message, the password must have recently been changed. Another option is to change your user profile configuration to prevent the password from expiring.
Oracle Error Message
The following are the error messages that will be displayed before the password expires and when the password is about to expire.
ORA-28002: the password will expire within 7 days 28002. 00000 - "the password will expire within %s 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.
How to change password in SQL Developer
Even if your user password is about to expire, you can still log in to Oracle. After logging in to Oracle, use the following command to reset the password. This will cause the password expiry data to be reset to the next interval. In SQL Developer, you can change the password for the user.
alter user <username> identified by <password>;Example
alter user scott identified by tiger;How to change password using SQL Plus
To reset the password, use the Oracle SQL Plus editor. SQL Plus is an Oracle database command line editor. You can use the sql plus editor to connect to Oracle and reset the password. The example below will demonstrate how to use sql plus to reset the password.
[oracle@localhost ~]$ sqlplus scott/tiger
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 10 21:06:47 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Thu Mar 10 2022 20:49:47 -05:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> password
Changing password for SCOTT
Old password:
New password:
Retype new password:
Password changed successfully
SQL>
How to remove the password expiry
When a database user is created, a profile is attached. The default profile name is “DEFAULT,” and it is automatically attached to new database users. The profile includes a password policy, and by default, passwords expire after 180 days. Modify the profile as follows to prevent this and allow users to keep their password indefinitely.
alter profile "DEFAULT" limit password_life_time unlimited;How to create new profile to remove the password expiry
The command below will create a new profile and apply the password policy to unlimited. If you do not want to change the password expiry for the current profile and want to create a new profile for one or more Oracle users, create a new profile with an unlimited password life time and attach the users to the profile. Password expiry for an Oracle user can thus be removed without changing the current profile.
create profile my_custom_profile limit password_life_time unlimited;
alter user <username> profile my_custom_profile;How to view the Oracle User Account status
It is critical to understand the status of the Oracle user account before and after the password is reset. The following command displays the Oracle user account status, which aids in confirming the password reset. If the password has expired, it will be marked as EXPIRED (GRACE). After the password has been reset, the status will be OPEN.
SQL> SELECT ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME='SCOTT'; ACCOUNT_STATUS -------------------------------- EXPIRED(GRACE) SQL> alter user scott identified by tiger; SQL> SELECT ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME='SCOTT'; ACCOUNT_STATUS -------------------------------- OPEN