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.

Oracle 11g Security
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.

Oracle Database Version Default Profile Values
Oracle 10g PASSWORD_LIFE_TIME: UNLIMITEDPASSWORD_LOCK_TIME: UNLIMITED

PASSWORD_GRACE_TIME: UNLIMITED

Oracle 11g PASSWORD_LIFE_TIME: 180PASSWORD_LOCK_TIME: 1

PASSWORD_GRACE_TIME: 7

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.

Get Free Blogging Tips & Technology updates in your Email !!!

Subscribe via RSS feed

The following two tabs change content below.
Anil Kumar
IT Blogger Tips focus on Blogging Tips, SEO Tips, Social Media, SQL Tips, PL/SQL Tips, Oracle DBA, Linux/Unix, Latest Technology, How Tos and Technical Solutions. You can find us on Facebook | Twitter |
Find on Google+

  • Anuradha

    Such clear step by step explanation..
    Helped a lot. Thank you !!

Siteground SALE
60% OFF SiteGround WordPress Hosting – $3.95/month (Exclusive Offer !!!)