Oracle 11g Security Features: ORA-28001: The Password Has Expired

My friend called me today and told about Ora-28001: The password has expired.

He told how it is possible.  How password can be expired in Oracle?

He was not aware about this security feature in Oracle 11g. He was treating it as an error message while it is a security feature of Oracle 11g. Many of us are not aware about this feature.

I am writing this post to aware my readers and newbie oracle administrators about this feature and how to resolve this, if it occurs with Oracle 11g Database.

Oracle code with detail description:

ORA-28001: the password has expired

Cause: The user’s account has expired and the password needs to be changed

Action: change the password or contact the DBA

First we need to understand default profiles of Oracle Database:

When you create a user in an instance, a default profile is assigned to that user. There are some differences between the default profiles for Oracle 10g and Oracle 11g Database.

Before Oracle 11g, the password limit (PASSWORD_LIFE_TIME) is not defined. In that case the password never expires for that particular user.

For Oracle 11g, the default password limit (PASSWORD_LIFE_TIME) has 180 days. PASSWORD_LOCK_TIME and PASSWORD_GRACE_TIME also have limits.

How to set the PASSWORD_LIFE_TIME to Unlimited?

If you want to set PASSWORD_LIFE_TIME limit to UNLIMITED, run following command to alter profile and set limit to UNLIMITED in Oracle 11g.

C:\ sqlplus sys/sys@xe as sysdba

Or

C:\sqlplus system/system@xe as sysdba

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

How to set new password if password expires?

If password expires then run following command on SYS or SYSTEM user.

Open command prompt and run following command.

C:\ sqlplus sys/sys@xe as sysdba

Or

C:\sqlplus system/system@xe as sysdba

SQL> ALTER USER TEST IDENTIFIED BY TEST;

Resolved ORA-12541: TNS no listener error

Fixed ORA-12518: TNS:listener could not hand off client connection

Oracle not started, database startup fail with ORA-16038, ORA-19809, ORA-00312 error

I hope you understood the article. If you have any query please write on comment section.