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];

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.

123 comments:

  1. Many Thanks! It solved my issue.

    ReplyDelete
  2. Thank you...that helped me....

    ReplyDelete
  3. Thanks .....really helpfull

    ReplyDelete
  4. thanks a lot ..... immediately solved my issue... grt solution !!!!

    ReplyDelete
  5. Is there a way to 'unexpire' the user passwords without changing them? I've got quite a few that have expired, but I don't know the original passwords for them all off hand (they are being used by programs and I don't want to have to dig them all up). I changed the PASSWORD_EXPIRE_TIME to UNLIMITED as recommended but the passwords that already expired before I did so are still in 'EXPIRED & LOCKED' status. Thanks!

    ReplyDelete
    Replies
    1. Friend,

      There is no option, other changing password and unlock it using:

      SQL> alter user identified by ;

      SQL> alter user account unlock;

      Thanks
      Umesh

      Delete
  6. Thank you, solved my problem

    ReplyDelete
  7. Good spot ... straight-forward fix. Thanks ...

    ReplyDelete
  8. Muchas gracias...

    ReplyDelete
  9. Thank you, it has helped mi in the right time

    ReplyDelete
  10. Actually you can capture user passwords for locked accounts.
    Use a query like this, before you reset it and unexpire it, then use the statement generated to set the password back to what it was, without ever knowing it...

    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;

    Can be very handy.

    ReplyDelete
  11. Thanks a lot. It helped.

    ReplyDelete
  12. Thanks a lot for this. It totally worked!!

    One note for people who might not have the set password for sysdba or sys and regularly use a third party client.

    Here's some info about logging into command line sqlplus without a password that helped me.

    SYS is a special user. Eventually you want to learn a bit about it's password requirements from http://docs.oracle.com/cd/E11882_01/server.112/e25494/dba007.htm#i1006789

    If you log on to the operating system on which the database s/w is installed, in command line as the user who owns the Oracle software, you will be able to run the command
    sqlplus / as sysdba

    to log in. The password is required when you log in across the network (and SQLNet has been setup correctly) or from an OS user that does not have 'DBA' privs.

    ReplyDelete
  13. even iam unable login to the DataBase then how can i execute these querys and commands.can you please help me out of yhis issue...

    ReplyDelete
  14. This comment has been removed by the author.

    ReplyDelete
  15. What error you are getting in login to the database ?

    ReplyDelete
  16. Thanks so much . This is just what I needed . I had Primavera go down due to Oracle password expiration .
    Cheers to the good work .

    ReplyDelete
  17. Chayan, Thanks for your appreciation.

    ReplyDelete
  18. Friend, You can write me at umesh049@gmail.com.

    ReplyDelete
  19. Thanks a lot. It helped me

    ReplyDelete
  20. Thanks a lot...This was really helpful

    ReplyDelete
  21. Awesome post! It helped me to resolve my issue.

    Thanks!
    Ananth.

    ReplyDelete
  22. Replies
    1. Hi Devender, Thanks for your comment :)

      Delete
  23. Helped me a lot :) Thanks!!
    -Chandra

    ReplyDelete
    Replies
    1. Hi Chandra, Thanks for your appreciation.

      Delete
  24. Many Many thanks!!
    Awesome post!

    ReplyDelete
  25. Replies
    1. Hi Friend, Thanks for your comment.

      Delete
  26. Thank you so much! Very easy to understand. It saved my day!

    ReplyDelete
    Replies
    1. Welcome Friend. Please, Keep on visiting this blog.

      Delete
  27. Hey There. I discovered your weblog the usage of msn.
    This is a really well written article. I will be sure to bookmark it and
    return to learn more of your helpful information.
    Thanks for the post. I will certainly comeback.

    My web blog; Http://Www.Goldenrat.Com/Fast-Products-For-Modcloth-Coupon-Code-The-Facts/

    ReplyDelete
  28. Thanks, it really helped me. This Site is awesome!

    ReplyDelete
    Replies
    1. Friend, Thanks for appreciation. Please, Keep on visiting this site.

      Delete
  29. Hiya very nice site!! Man .. Excellent .. Amazing .
    . I'll bookmark your site and take the feeds additionally? I am glad to search out so many useful information right here in the post, we want develop extra strategies on this regard, thanks for sharing. . . . . .

    my page; hot penny stocks to buy ()

    ReplyDelete
  30. Sweet blog! I found it while surfing around on Yahoo News.
    Do you have any tips on how to get listed in Yahoo News?

    I've been trying for a while but I never seem to get there! Appreciate it

    Also visit my web site - binary stock options

    ReplyDelete
  31. Greate pieces. Keep posting such kind of info on your page.
    Im really impressed by your blog.
    Hello there, You have performed a great job. I will definitely digg it
    and in my opinion recommend to my friends. I'm confident they will be benefited from this site.

    Here is my website password Finder

    ReplyDelete
  32. Woah! I'm really digging the template/theme of this blog. It's simple, yet effective.
    A lot of times it's very hard to get that "perfect balance" between superb usability and appearance. I must say that you've done a very good job with
    this. Also, the blog loads extremely fast for me on Internet explorer.

    Superb Blog!

    My homepage :: wedding arch

    ReplyDelete
  33. Worked like a charm...
    Thanks Umesh Sharma.

    ReplyDelete
  34. thank you very much !!!! was very usefull !!!!

    ReplyDelete
  35. Amazing thread :)...

    I have a few accounts expired but i can't change them to open using alter user account unlock :(.... why is this?

    Thanks so much and it's added to my favorites :)

    ReplyDelete
  36. Thanks so much!!!...

    Amazing thread..

    Quick question; i have 5 accounts expired so my BI system has stopped working correctly.

    Why i can't change the status of those accounts using "alter [user] account unlock"?

    Thanks so much!!!

    ReplyDelete
    Replies
    1. Are you getting any error after executing this command ?

      Delete
    2. Hi Umesh :)

      Nop, after i execute the commands, it appears the sql> so i believe that it is doing it but when i run a query to check the current status of the user, it still says EXPIRED

      Delete
    3. So sorry umesh but it was my mistake :).

      Now my BI server is running :).

      Thanks so much and this is going to my favorites :)

      Delete
  37. thanks a lot!!!!!!!!!!!! solved my problem in seconds!!

    ReplyDelete
  38. hey
    why i cannot alter this user
    alter user "XS$NULL" identified by values 'DC4FCC8CB69A6733';
    as after i did all these steps , the EM still shows thw same ora ???
    For your kind help please .

    ReplyDelete
  39. Thank You Sir! It saved me a lot of time.

    ReplyDelete
  40. WOW just what I was searching for. Came here by searching for whatsapp hack (Youtube.com)

    ReplyDelete
  41. Thanks a lot !!!

    ReplyDelete
  42. Very Nice!! 1 question after running the command to be prompted to enter the value for username, and then it generates the alter command for that user.
    if i try to run it again in the same sql session, it does not prompt and gives me the same alter user command for the userid i gave it the first time.
    always have to close sql session and start a new one to get it to do another username..?
    thanks fot the help

    ReplyDelete
  43. Hi Umesh!
    The password expiration query worked perfectly well.I am new to Oracle & installed 12c version lately.I have been trying to logon to the default username & password "scott/tiger" but I always get an error of invalid username/password.So I tried resetting password commands to keep the same password:

    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;

    for which I got "No rows selected".

    How come user "scott" doesn't show up? Please clarify my doubt.

    Thanks!!

    ReplyDelete
    Replies
    1. hi,

      First of all, let's check if scott user exists or not using:-

      sql > select * from dba_users;

      if this query lists scott user then try scott and SCOTT, may this is because of case sensitivity.

      Thanks,
      Umesh

      Delete
  44. thank you very much.
    solved my issue in no time... great solution!

    -joeyp/skf singapore

    ReplyDelete
  45. I every time emailed this website post page to all my associates, because if like to read it then my links will
    too.

    ReplyDelete
  46. Generally I do not learn article on blogs, but
    I would like to say that this write-up very forced me to
    try and do so! Your writing taste has been surprised
    me. Thank you, very nice article.

    ReplyDelete
  47. Thankyou so much umesh it's more helpful to me.
    If you have a time can you please teach me SQL DBA course.

    ReplyDelete
    Replies
    1. Hi Friend, I don't work on SQL Database So can't help you. Thanks.

      Delete
  48. Thank You Very Much!!

    ReplyDelete
  49. thank you very much.
    solved my issue in no time... great solution!

    ReplyDelete
  50. Thank you very much.
    Solved my issue in no time. Great solution!

    ReplyDelete
  51. Thanks for the detailed steps ..it really helped.

    ReplyDelete
  52. Thank you so much, saved a day.

    -K

    ReplyDelete
  53. Thanks. This was a life-saver!

    ReplyDelete
  54. It's really helpful. Many Many Thanks!!

    ReplyDelete
  55. Yay! Great post! Solved my problem!!

    ReplyDelete
  56. Thanks a ton, Mr. Umesh Sharma! :) It really helped me to solve my trouble! :)

    ReplyDelete
  57. Muchas gracias!!!

    ReplyDelete
  58. Very helpful thanks

    ReplyDelete
  59. That was very helpful. Thanks.

    ReplyDelete
  60. This is really helpful !!
    thanks

    ReplyDelete
  61. is there a way to reset the password when the password starts with the @ symbol?

    ReplyDelete
  62. Thank you very much.
    Solved my issue in no time. Great solution!

    ReplyDelete
  63. Thanks a lot, it really helped me to save time and from lot of worries

    ReplyDelete
  64. Bull's eye.. precise and to the point solution. Got my issue resolved!! Thanks a lot!

    ReplyDelete
  65. how i can pleas In the space below, type the confirmation number that was given when you applied.
    Format

    ReplyDelete
  66. Nice one really appreciate the elaboration.

    ReplyDelete
  67. Thank you! This was very helpful

    ReplyDelete
  68. how I connect to database using sys users?

    ReplyDelete
  69. you are Awesome......where to learn these pipelined query.........
    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;

    ReplyDelete
  70. thanks
    i do it and the user become unlock but when i try to enter to the programm i get erre : 503 servic unavalable
    can you help me

    ReplyDelete
  71. thanks
    i solved the problem and the user become unlock but when i try to enter via network i get error : 503 service unaavailable
    can you helpe me

    ReplyDelete
  72. Merci, j'ai résolu mon problème.

    ReplyDelete