Oracle Database 11g Passward Expire Problem

Different from other releases, Oracle 11g sets by default password expiration.

Suppose you have an user with the status "expired":

Please check at:

1) SQL> select username, account_status, expiry_date, profile from dba_users;

USERNAME             ACCOUNT_STATUS       EXPIRY_DA PROFILE
-------------------- -------------------- --------- ----------
SOE                  OPEN                 18-JAN-12 DEFAULT
LOUL                 EXPIRED              18-JAN-11 DEFAULT
SCOTT                EXPIRED & LOCKED     15-JUL-11 DEFAULT
ORACLE_OCM           EXPIRED & LOCKED     05-SEP-10 DEFAULT
XS$NULL              EXPIRED & LOCKED     05-SEP-10 DEFAULT
MDDATA               EXPIRED & LOCKED     05-SEP-10 DEFAULT
DIP                  EXPIRED & LOCKED     05-SEP-10 DEFAULT
APEX_PUBLIC_USER     EXPIRED & LOCKED     05-SEP-10 DEFAULT

2) SQL> alter user loul identified by netapp;

3) To avoid to have users with "expired" password, you have two options:

- Create a new profile and set your own definitions about dates to expire the passwords
- Change the default profile

4) Let's make an example how to do that:

Checking profiles:

SQL> SELECT profile, resource_name, limit FROM dba_profiles WHERE profile='DEFAULT';

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

PROFILE    RESOURCE_NAME                    LIMIT
---------- -------------------------------- ----------------------------------------
DEFAULT    PASSWORD_REUSE_TIME              UNLIMITED
DEFAULT    PASSWORD_REUSE_MAX               UNLIMITED
DEFAULT    PASSWORD_VERIFY_FUNCTION         NULL
DEFAULT    PASSWORD_LOCK_TIME               1
DEFAULT    PASSWORD_GRACE_TIME              7

5) Changing the password_life_time for unlimited

SQL> alter profile default limit password_life_time unlimited;

--------------OR-----------

For development you can disable password policy if no other profile was set (i.e. disable password expiration in default one):

ALTER PROFILE "DEFAULT" LIMIT PASSWORD_VERIFY_FUNCTION NULL;

Then, reset password and unlock user account. It should never expire again:

--------------END-----------