Steps To Fix Oracle Password Expired Error

Oracle Database

When trying to login to database as schema user you might see this error and this might cause the application to stop working.


ADVERTISEMENT

Error Seen:

ORA-28001: THE PASSWORD HAS EXPIRED

 

Root Cause:

This error comes due to the Oracle password policy for database users. All passwords are tend to expire after certain time period if the password expiry time is not set to unlimited.

Workaround/Solution:

Follow below steps to set the password expiry to unlimited and not to expire.

1. Login to Oracle database as oracle or admin user.

2. Execute below commands to extend the expiry to UNLIMITED.

	
$ sqlplus /nolog
SQL> conn /as sysdba;
SQL> ALTER PROFILE DEFAULT LIMIT COMPOSITE_LIMIT UNLIMITED PASSWORD_LIFE_TIME UNLIMITED PASSWORD_REUSE_TIME UNLIMITED PASSWORD_REUSE_MAX UNLIMITED PASSWORD_VERIFY_FUNCTION NULL PASSWORD_LOCK_TIME UNLIMITED   PASSWORD_GRACE_TIME UNLIMITED FAILED_LOGIN_ATTEMPTS UNLIMITED;
SQL> alter user USERNAME account unlock;
SQL> exit;

In case the user is not part of default profile you can add the user to default profile using below command.

ALTER USER USERNAME PROFILE DEFAULT;

or create a brand new profile which will have no password expiry and assign the user to that profile.

CREATE PROFILE UNLIMITED_ACCESS LIMIT COMPOSITE_LIMIT UNLIMITED PASSWORD_LIFE_TIME UNLIMITED PASSWORD_REUSE_TIME UNLIMITED PASSWORD_REUSE_MAX UNLIMITED PASSWORD_VERIFY_FUNCTION NULL PASSWORD_LOCK_TIME UNLIMITED   PASSWORD_GRACE_TIME UNLIMITED FAILED_LOGIN_ATTEMPTS UNLIMITED;

ALTER USER USERNAME PROFILE UNLIMITED_ACCESS;

You can check the changes done to profile using below command:

select * from dba_profiles where profile='DEFAULT';

ORA-12154: TNS:COULD NOT RESOLVE THE CONNECT IDENTIFIER SPECIFIED

In case of any ©Copyright or missing credits issue please check CopyRights page for faster resolutions.

Leave a Reply