This discussion is archived
10 Replies Latest reply: May 27, 2010 12:44 PM by 693783 RSS

ORA-28002: the password will expire within string days

fsze88hoho Newbie
Currently Being Moderated
Experts,
OS : centOS5, oracle 11g
I discovered an error when I try to login my oracle schema,
sqlplus kennam/******@kennam
It give me an error ORA-28002: the password will expire within string 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 DBA
http://download.oracle.com/docs/cd/B28359_01/server.111/b28278/e24280.htm
I fixed it already by alter user kennam identified by ******
But,.... I would like to know when will happen again later on? (how long will that schema to be expire again)?

This is the first time I meet this happen
Thanks ;)
  • 1. Re: ORA-28002: the password will expire within string days
    584650 Explorer
    Currently Being Moderated
    Check the user's profile, you will find that in 11g the default expiration date (if they are using the default profile) has changed from UNLIMITED to 180 days. You probably just crossed the 180 days and have entered the GRACE period for the password. After the N amount of grace days (password_grace_time) you will be forced to change the password. I am guaranteeing there will be applications out there that will not be able to deal with this (changing the password because expiry time has been reached).

    I'm trying to find a way to change the expiry date for current users without having to set a password for that user again and setting the password_expire_time to UNLIMITED in the profile doesn't update the EXPIRY_DATE in DBA_USERS.
  • 2. Re: ORA-28002: the password will expire within string days
    Justin Cave Oracle ACE
    Currently Being Moderated
    You need to find out what profile is assigned to your user
    SELECT profile
      FROM dba_users
     WHERE username = 'KENNAM'
    Then you'll want to look at the PASSWORD_LIFE_TIME and PASSWORD_GRACE_TIME parameters in the DBA_PROFILES table for the profile name identified above.

    Justin
  • 3. Re: ORA-28002: the password will expire within string days
    Mohammed Mehraj Hussain Journeyer
    Currently Being Moderated
    that is depends on the profile setting..
    what is the profile of that user..

    u can chech in dba_users .
    and by selecting that profile u can check it..

    SQL> select resource_name,liMit from dba_profiles where profile='DEFAULT';

    RESOURCE_NAME LIMIT
    -------------------------------- ----------------------------------------
    COMPOSITE_LIMIT UNLIMITED
    SESSIONS_PER_USER UNLIMITED
    CPU_PER_SESSION UNLIMITED
    CPU_PER_CALL UNLIMITED
    LOGICAL_READS_PER_SESSION UNLIMITED
    LOGICAL_READS_PER_CALL UNLIMITED
    IDLE_TIME UNLIMITED
    CONNECT_TIME UNLIMITED
    PRIVATE_SGA UNLIMITED
    FAILED_LOGIN_ATTEMPTS 10
    PASSWORD_LIFE_TIME UNLIMITED

    RESOURCE_NAME LIMIT
    -------------------------------- ----------------------------------------
    PASSWORD_REUSE_TIME UNLIMITED
    PASSWORD_REUSE_MAX UNLIMITED
    PASSWORD_VERIFY_FUNCTION NULL
    PASSWORD_LOCK_TIME UNLIMITED
    PASSWORD_GRACE_TIME UNLIMITED

    expire_time=PASSWORD_LIFE_TIME
  • 4. Re: ORA-28002: the password will expire within string days
    fsze88hoho Newbie
    Currently Being Moderated
    Yes, you are right,
    I have one more question, am I possible(the right) to change the field PASSWORD_LIFE_TIME to be UNLIMITED from dba_profiles of that profile (DEFAULT)?
    Many thanks
    SQL> select resource_name,resource_type, limit from dba_profiles where profile='
    DEFAULT';
    
    RESOURCE_NAME                    RESOURCE LIMIT
    -------------------------------- -------- ----------
    COMPOSITE_LIMIT                  KERNEL   UNLIMITED
    SESSIONS_PER_USER                KERNEL   UNLIMITED
    CPU_PER_SESSION                  KERNEL   UNLIMITED
    CPU_PER_CALL                     KERNEL   UNLIMITED
    LOGICAL_READS_PER_SESSION        KERNEL   UNLIMITED
    LOGICAL_READS_PER_CALL           KERNEL   UNLIMITED
    IDLE_TIME                        KERNEL   UNLIMITED
    CONNECT_TIME                     KERNEL   UNLIMITED
    PRIVATE_SGA                      KERNEL   UNLIMITED
    FAILED_LOGIN_ATTEMPTS            PASSWORD 10
    PASSWORD_LIFE_TIME               PASSWORD 180
    
    RESOURCE_NAME                    RESOURCE LIMIT
    -------------------------------- -------- ----------
    PASSWORD_REUSE_TIME              PASSWORD UNLIMITED
    PASSWORD_REUSE_MAX               PASSWORD UNLIMITED
    PASSWORD_VERIFY_FUNCTION         PASSWORD NULL
    PASSWORD_LOCK_TIME               PASSWORD 1
    PASSWORD_GRACE_TIME              PASSWORD 7
  • 5. Re: ORA-28002: the password will expire within string days
    PavanKumar Guru
    Currently Being Moderated
    Hi,

    You can as per requirement.

    The number of days the same password can be used for authentication
    password_life_time <value | UNLIMITED | DEFAULT>

    - Pavan Kumar N
  • 6. Re: ORA-28002: the password will expire within string days
    fsze88hoho Newbie
    Currently Being Moderated
    hum.....
    No,
    ORA-01733 virtual column not allowed here
    Cause: An attempt was made to use an INSERT, UPDATE, or DELETE statement on an expression in a view.
    Action: INSERT, UPDATE, or DELETE data in the base tables, instead of the view.
    http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96525/e1500.htm
    [oracle@localhost ~]$ export ORACLE_SID=kennam
    [oracle@localhost ~]$ sqlplus / as sysdba
    SQL> update dba_profiles set limit='UNLIMITED' where profile='DEFAULT' and RESOURCE_NAME='PASSWORD_LIFE_TIME';
    update dba_profiles set limit='UNLIMITED' where profile='DEFAULT' and RESOURCE_NAME='PASSWORD_LIFE_TIME'
                            *
    ERROR at line 1:
    ORA-01733: ???????????
    Any idea(s)? ;)
  • 7. Re: ORA-28002: the password will expire within string days
    PavanKumar Guru
    Currently Being Moderated
    Hi,

    ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

    - Pavan Kumar N
  • 8. Re: ORA-28002: the password will expire within string days
    fsze88hoho Newbie
    Currently Being Moderated
    Dear Stellios,Justin Cave,Mohd Mehraj Hus...,Pavan Kumar
    Thank you, all of you was helping to solve my problem and wasted your valuable time ;)
  • 9. Re: ORA-28002: the password will expire within string days
    656804 Newbie
    Currently Being Moderated
    Yep. The statement to change the profile is certainly

    ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

    However, my question is despite changing the profile, my users are still getting the warning about password expiration.
    ORA-28002: the password will expire within 7 days


    I understand that if they reset it now, the problem shouldn't occur.
    But is there a way to have the existing users not get this warning anymore without resorting to resetting the password ?

    -AS
  • 10. Re: ORA-28002: the password will expire within string days
    693783 Newbie
    Currently Being Moderated
    Hi ,

    ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

    the above stmt will ensure that the users who are assigned with default profile shldn't be getting issues.


    Thx
    JP

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points