Solution – Oracle 11g Password Expiry ORA-28002

ERROR: ORA-28002: the password will expire within 6 days. Lets see what we can do about resolving this issue.

You could simply just change the users password. In an ideal and secure world, this is exactly what you would do. Though this may not be practical, there may be a whole host of reasons for postponing the change.

In Oracle 11g, users are assigned to a profile, the default profile is named “DEFAULT”. This default profile comes configured with a maximum password lifetime of a 180 days.

[box type="info"] Just note that this document’s steps have been performed on Oracle 11g Release 11.2.0.3.0 running on Red Hat Enterprise Linux 6.3. The SQL syntax provided “should” work on all Oracle 11g platforms.[/box]

Step 1: Identify the Users Profile

Log into Oracle 11g using SQL*Plus tool as sysdba

Lets confirm that the user is running the default profile. Note you must capitalize the username.

As you can see, the user has the profile of “DEFAULT” configured.

Step 2: View the Profile settings

Lets examine the settings of the profile

As you can see the PASSWORD_LIFE_TIME is to 180 days, you may configure this however you want.

Step 3: Set PASSWORD_LIFE_TIME

In this example, we’re going to make the password life unlimited so that it never expires. This may not be the wisest thing to do in a production environment. However there may be circumstances that require a password change at a later date.

To make this change we simply need to run the following command:

Step 4: Re-Enter the Password

You may notice that even after setting the password expiry to unlimited you are still getting the “ERROR: ORA-28002: the password will expire” message. I’m guessing this is due to additional processes that Oracle does in the background for checking password age etc. So this forces us to “reset” the password to it’s current value to remove the error.

This is done via the following SQL

Just note that the password must be double quoted and not single quoted.

…and that’s it, you should no longer receive the “ORA-28002: the password will expire” message for USER1 when logging in.

[box type="warning"] Just note that having never expiring passwords probably isn’t the best password policy to maintain. Oracle’s password expiration setting is a great way to remind users and admin’s to change their passwords regularly.[/box]

[author] [author_image timthumb='on']http://mcdee.com.au/wp-content/uploads/2012/11/photo.jpg[/author_image] [author_info]Andrew McDonald is an IT Systems Admin and all round technology junkie. Absolutely a jack-of-all-trades and not one to shy away from a challenge.


 [/author_info] [/author]

  7 comments

  1. Ricardo Heavyrick   •  

    It worked man o/

    i have three users in my database and i had to change the password from all of them, one by one. After that the errors had gone. Thank you so much o/

  2. Richard   •  

    Thanks Andrew, that fixed my problem, much appreciated.

  3. Felix M   •  

    This solution worked for me. Thanks heaps!

  4. Karthik   •  

    The post is amazing…even for a person who is totally unfamiliar with databases, he can easily use this information to solve the issue

  5. able   •  

    Nice Explanation.

  6. pete   •  

    Excellent step by step guide. Thanks!

  7. charles peirce   •  

    SQL is as far out of my comfort zone as I can get and still be on the computer. I have to support legacy software we sold so long ago that the writers have all moved on to other companies, without leaving any documentation. So the first thing I see is “ORA-28002: the password will expire within 7 days”.

    The account is used by other programs, so a password change would create a whole cascade of problems.

    You saved me from digging myself into a hole – if ever we meet, remind me that I owe you a pint.

Leave a Reply

Your email address will not be published. Required fields are marked *


3 × = three

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">