ORA-28002: the password will expire within 7 days

ORA-28002: the password will expire within 7 days
Cause: The user's account is about to about to expire and the password needs 
to be changed.
Action: Change the password or contact the database administrator.

Reference: Oracle Documentation 

Solutions:

1) Simply change the password to avoid it temporary

  [oracle@lnxsvr ~]$ sqlplus scott/tiger

  SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 20 14:08:01 2012

  Copyright (c) 1982, 2009, Oracle.  All rights reserved.

  ERROR:
  ORA-28002: the password will expire within 7 days

  Connected to:
  Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

  SQL> PASSWORD
  Changing password for SCOTT
  Old password:
  New password:
  Retype new password:
  Password changed


2) Set PASSWORD_LIFE_TIME of the profile assigned user to UNLIMITED 
   then change the password to avoid it permanently
   
  [oracle@lnxsvr ~]$ sqlplus scott/tiger

  SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 20 14:08:01 2012

  Copyright (c) 1982, 2009, Oracle.  All rights reserved.

  ERROR:
  ORA-28002: the password will expire within 7 days

  Connected to:
  Oracle Database 11g Release 11.2.0.1.0 - 64bit Production   

  SQL> connect / as sysdba
  Connected.
  
  SQL> SELECT PROFILE FROM dba_users WHERE username = 'SCOTT';

  PROFILE
  ------------------------------
  DEFAULT

  SQL> SELECT  LIMIT FROM DBA_PROFILES WHERE PROFILE='DEFAULT' 
  AND RESOURCE_NAME='PASSWORD_LIFE_TIME';

  LIMIT
  ----------------------------------------
  60

  SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

  Profile altered.

  SQL> SELECT ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME='SCOTT';

  ACCOUNT_STATUS
  --------------------------------
  EXPIRED(GRACE)

  SQL> connect scott/tiger
  Connected.
  
  SQL> PASSWORD
  Changing password for SCOTT
  Old password:
  New password:
  Retype new password:
  Password changed

- Oracle Default Username and Password
- How to connect sqlplus without tnsnames.ora
- SQLNET: How does Oracle Client connect with Oracle Server
- How to configure Case Sensitive Password in Oracle
- ORA-01017: invalid username/password; logon denied- ORA-03135: connection lost contact
- ORA-01034: ORACLE not available
- ORA-01031: insufficient privileges

41 comments:

  1. I am new to Oracle. Your solution worked!!
    Thanks.

    Nisant

    ReplyDelete
  2. i am new to oracle....
    i have worked in ur codes... but get errors like
    invalid old password
    and table or view not exists....
    please help me getting out of this problem....
    it shows only two days remains to expire,,,,,
    please..........

    ReplyDelete
    Replies
    1. show me the steps you have made

      Delete
    2. 1st solution .. it gives
      CONNECTED TO:
      ORACLE DATABASE 11G ENTERPRISE EDITION RELEASE 11.2.0.1.0-PRODUCTION WITH THE PARTITIONIG... ETC
      SQL>PASSWORD
      OLD PSWD:TIGER
      NEW PSWD :VINDYA
      RETYPE NEW...
      ERROR:
      ORA-28008: INVALID OLD PSWD


      but, for the entering sql,user name and pswd are scott and tiger only....

      then for solution 2...
      i acted as per ur code... it gives
      table or view doesnt exists.....


      m using windows 7 os..



      Delete
    3. i am using windows 7 os....
      is that any problem ...

      and for the first solution...
      connected to...oracle database 11g enterprise edition. release..11.2.0.1.0.
      i gave the password cmd... then all the vaaalues taken... after entering retyping new pswd.. it shows
      ora error:28008: invalid old passwod


      for the 2 nd solution...
      it gives the error ora-00942 table or view doesnt exists..
      pleae help me.... sir...


      Delete
    4. Ora-28008 says that your old password in incorrect. In 11g password are case sensitive. you may need to type old password in small.

      Delete
  3. hi... sir... i serached in many ways.... to list out all the tables....
    and found that there is no such tables like...dba_users...there...
    wht to do now sir...?

    ReplyDelete
    Replies
    1. dba_users is a system table, scott user dont have permission to access that table. check the post again. here we are connected with sys users as sysdba while accessing dba_users

      Delete
  4. sir if i dont change this then will it not work after 7days?

    ReplyDelete
  5. Yes, it worked!!! thanks a lot!!!

    ReplyDelete
  6. Hi Nimish,

    Thanks a lot!! it worked for me :)

    ReplyDelete
  7. You saved my ass, thx

    ReplyDelete
  8. I write command for expired the password and then i entered new password like tiger it is working so further it will work or not ?

    ReplyDelete
    Replies
    1. Set PASSWORD_LIFE_TIME of the profile assigned user to UNLIMITED then change the password to avoid it permanently

      Delete
  9. thats marvelous......worked straight away.....

    ReplyDelete
  10. Thats marvelous. Worked straight away......Thankyou....

    ReplyDelete
  11. during installation of oracle i didnt unlock the sysdba .so i have logged in as "system" and i have done the steps mentioned.i was successfull in it.but now i have got a new problem.now my "user-system" password is about to expire within 7 days.i have done the same proceess for username system,but there it is showing me the password lifetime is unlimited.could u plz tell me how to overcome this problem

    ReplyDelete
    Replies
    1. ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; makes password lifetime to unlimited so what is the issue you are facing here ?

      Delete
  12. Thanks for the solution. It worked

    ReplyDelete
  13. Thanks! It worked

    ReplyDelete
  14. Hi sir,

    im trying to get the message ORA 28002 on a on_message trigger and when i try to do an exit_form it send me an error like illegal restricted procedure exit_form..... I want to force to user to chenge the passwd but in a diferent way, so I need that after get that message it exit the form.

    I apreciate your help

    ReplyDelete
  15. HOW DO I DO THIS

    ReplyDelete
  16. Thanks!Works perfectly fine.

    ReplyDelete
  17. alter user SCHEMA NAME identified by SCHEMA PASSWORD;

    ReplyDelete
  18. Your solution worked..Thank you..!

    ReplyDelete
  19. Thats awesome solution you are great

    ReplyDelete
  20. Thanks for your Good solution.

    ---------------------------------------------------

    ReplyDelete
  21. dude ur rookie... really good solution

    ReplyDelete
  22. Thankyou ...Worked for me!!!

    ReplyDelete
  23. i changed the password as it was about to expire. Now i get this error ORA-12543 destination host unreachable. Why is it so?

    ReplyDelete