ORA-28001: the password has expired
Applies to:
Oracle Database Software Std & Enterprise Edition - Version: 11.1.1.1.0 and later [Release: 11gR1 and later ]Information in this document applies to any platform.
Reason:
In Oracle Database, A remote DBA face this because password have reached 180 Default limit for Password life time. The life of a password is defined as 180 days by default. DBA has to change this limit to unlimited to solve this and to avid this in future. Before getting this error DBA will also see ORA-28002: the password will expire within n days I would suggest to change password before it expires.
Solutions:
1. Connect to database using sys users.
2. Execute the following query
Sql > select * from dba_profiles;
the output of this query will be like.
PROFILE RESOURCE_NAME RESOURCE LIMIT -------------------- ------------------------- -------- --------------- MONITORING_PROFILE COMPOSITE_LIMIT KERNEL DEFAULT DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED MONITORING_PROFILE SESSIONS_PER_USER KERNEL DEFAULT DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED MONITORING_PROFILE CPU_PER_SESSION KERNEL DEFAULT DEFAULT CPU_PER_SESSION KERNEL UNLIMITED MONITORING_PROFILE CPU_PER_CALL KERNEL DEFAULT DEFAULT CPU_PER_CALL KERNEL UNLIMITED MONITORING_PROFILE LOGICAL_READS_PER_SESSION KERNEL DEFAULT DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED MONITORING_PROFILE LOGICAL_READS_PER_CALL KERNEL DEFAULT PROFILE RESOURCE_NAME RESOURCE LIMIT -------------------- ------------------------- -------- --------------- DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED MONITORING_PROFILE IDLE_TIME KERNEL DEFAULT DEFAULT IDLE_TIME KERNEL UNLIMITED MONITORING_PROFILE CONNECT_TIME KERNEL DEFAULT DEFAULT CONNECT_TIME KERNEL UNLIMITED MONITORING_PROFILE PRIVATE_SGA KERNEL DEFAULT DEFAULT PRIVATE_SGA KERNEL UNLIMITED MONITORING_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10 MONITORING_PROFILE PASSWORD_LIFE_TIME PASSWORD DEFAULT DEFAULT PASSWORD_LIFE_TIME PASSWORD 180 PROFILE RESOURCE_NAME RESOURCE LIMIT -------------------- ------------------------- -------- --------------- MONITORING_PROFILE PASSWORD_REUSE_TIME PASSWORD DEFAULT DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED MONITORING_PROFILE PASSWORD_REUSE_MAX PASSWORD DEFAULT DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED MONITORING_PROFILE PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL MONITORING_PROFILE PASSWORD_LOCK_TIME PASSWORD DEFAULT DEFAULT PASSWORD_LOCK_TIME PASSWORD 1 MONITORING_PROFILE PASSWORD_GRACE_TIME PASSWORD DEFAULT DEFAULT PASSWORD_GRACE_TIME PASSWORD 7 32 rows selected.Here PASSWORD_LIFE_TIME field is responsible for expiring of password after 180 days.
3. execute following command to disable this feature:
Sql> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
4. Now crosscheck for disabling of this feature.
Sql > select * from dba_profiles;
The value in PASSWORD_LIFE_TIME has changed to unlimited. Now password will never expire.
5. Now reset the password of locked user. Here are two options to reset password. Either you can keep same password or you can give a new password.
To Keep same Password:
SQL> select 'alter user "'||d.username||'" identified by values '''||u.password||''';' c
from dba_users d, sys.user$ u
where d.username = upper('&&username')
and u.user# = d.user_id;
Enter value for username: scott
old 3: where d.username = upper('&&username')
new 3: where d.username = upper('scott')
C
--------------------------------------------------------------------------------
alter user "SCOTT" identified by values 'F894844C34402B67';
SQL> alter user "SCOTT" identified by values 'F894844C34402B67';
To give a new Password:
sql> alter user [user_name] identified by [password];
5. Now reset the password of locked user. Here are two options to reset password. Either you can keep same password or you can give a new password.
To Keep same Password:
SQL> select 'alter user "'||d.username||'" identified by values '''||u.password||''';' c
from dba_users d, sys.user$ u
where d.username = upper('&&username')
and u.user# = d.user_id;
Enter value for username: scott
old 3: where d.username = upper('&&username')
new 3: where d.username = upper('scott')
C
--------------------------------------------------------------------------------
alter user "SCOTT" identified by values 'F894844C34402B67';
SQL> alter user "SCOTT" identified by values 'F894844C34402B67';
To give a new Password:
sql> alter user [user_name] identified by [password];
6. Unlock user account using below command
sql> alter user [User_name] account unlock;
7. Crosscheck by value of accout_status field in dba_users view.
sql> select username,account_status from dba_users;
The value of account_status filed should by "OPEN" for corresponding user.
