ORA-28001: the password has expired

Sometime we face issue in Oracle, we got complaints from application team that our oracle user is expired every 30 days. DBA need to configured the profile of USER according to the environmental need.

Following are the steps to check user and make it profile to never expired:

1. Check the USER and PROFILE need to changed.

SELECT username, account_status, profile FROM dba_users WHERE ACCOUNT_STATUS LIKE '%EXPIRED%';

-- find profile of specific user
SELECT PROFILE FROM DBA_USERS WHERE USERNAME = 'HR';


2. Verify the profiles associated with different users.
Note: If yes then you change the profile it will affect all other user associated with that profile

--Verify user present in that profile.
SELECT USERNAME FROM DBA_USERS WHERE profile = 'DEFAULT';


3. Check the current status of profiles

SELECT resource_name,limit FROM dba_profiles WHERE profile='DEFAULT';

Note: PASSWORD_LIFE_TIME field is responsible for expiring of password after 180 days.

4. Change the limit of password to unlimited:
Note: Using DEFAULT profile, you can check your profile in 1 step.

-- PASSWORD LIFE TIME No of days when password expired
alter profile DEFAULT limit password_life_time UNLIMITED;
-- PASSWORD REUSE TIME: Same password is used multiple time
alter profile DEFAULT limit PASSWORD_REUSE_TIME unlimited;


5. Verify the change of Profiles:

SELECT resource_name,limit FROM dba_profiles WHERE profile='DEFAULT';


6. Change the password or use old password and fire the command:

-- If you know the password
alter user DBUSER identified by newpa$$word;

--Reset password with this command:
alter user dbuser identified by values 'password';


7. Check the user is good to go

SELECT username, account_status, EXPIRY_DATE FROM DBA_USERS WHERE username='DBUSER';