How to change expired password in Oracle?

password
In Oracle when the password is expired you will get the following error:
ORA-28001: the password has expired
This is because the password has reached 180 Default limit for a Password lifetime.
Solutions:
1.  Connect to the database using sys users.
2. Execute the following query
Sql > select * from dba_profiles;
the output of this query will be like.
 
Here PASSWORD_LIFE_TIME field is responsible for expiring of the password after 180 days.
3.  Execute the following command to disable this feature:
Sql> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
4. Now crosscheck for disabling this feature.
Sql > select * from dba_profiles;
 
The value in PASSWORD_LIFE_TIME has changed to unlimited. Now password will never expire.
5. Now change the password of the locked user and unlock using the following.
sql> alter user [user_name] identified by [password];sql> alter user [User_name] account unlock;

6. Crosscheck by the value of accout_status field in dba_users view.

sql> select username,account_status from dba_users;

The value of the account_status filed should be “OPEN” for the corresponding user.

Leave a Reply

%d bloggers like this: