ORA-28002: the password will expire within days oracle | has expired

ORA-28002: the password will expire within n days is an Oracle warning message indicating that a user's password is about to expire. It is a notification that the password is still valid, but it will expire soon (the exact number of days is mentioned in the error message).

To solve this, you can either change the password before it expires or modify the password expiration policy. Here are the steps for both approaches:

1. Changing the Password

You can change the user's password using the following SQL command:

ALTER USER username IDENTIFIED BY new_password;

For example, if the username is john and the new password is john@123, the command would be:

ALTER USER john IDENTIFIED BY john@123;

This resets the user's password, and the expiration warning will no longer appear.

2. Modifying the Password Expiration Policy

If you don't want passwords to expire (or you want to extend the expiration period), you can modify the profile that controls password expiry.

Steps to check the password expiration policy:

Query the profile to see the current password expiration settings:

SELECT PROFILE, PASSWORD_LIFE_TIME FROM DBA_PROFILES WHERE RESOURCE_NAME = 'PASSWORD_LIFE_TIME';

This will show you how long passwords are valid before they expire.

To disable password expiration:

You can modify the profile to prevent passwords from expiring by setting PASSWORD_LIFE_TIME to UNLIMITED:

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

To extend the password expiry period:

If you want to extend the password expiry period (e.g., to 180 days), use:

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 180;

This will ensure that passwords expire after the specified number of days.

3. Preventing the Warning for a Specific User

If you want to set the policy for a specific user, you can assign the user a custom profile with a different password expiration setting. Here's an example:

Create a custom profile:

CREATE PROFILE my_profile LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Assign the profile to the user:

ALTER USER username PROFILE my_profile;

This would prevent the password expiration warning for that specific user.