Schema Password Lifetime

In oracle database schema password lifetime managed by the user profile, before short time the expiry of limit user will get ORA-28002: the password will expire within 7 days alter.

SQL> SELECT U.PROFILE, RESOURCE_NAME, LIMIT
  2  FROM DBA_USERS U, DBA_PROFILES P
  3  WHERE U.PROFILE=P.PROFILE
  4  AND USERNAME = 'SCOTT'
  5  AND RESOURCE_NAME='PASSWORD_LIFE_TIME';

PROFILE                        RESOURCE_NAME                    LIMIT
------------------------------ -------------------------------- ----------------------------------------
DEFAULT                        PASSWORD_LIFE_TIME               60

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 180;

Profile altered.

SQL> SELECT U.PROFILE, RESOURCE_NAME, LIMIT
  2  FROM DBA_USERS U, DBA_PROFILES P
  3  WHERE U.PROFILE=P.PROFILE
  4  AND USERNAME = 'SCOTT'
  5  AND RESOURCE_NAME='PASSWORD_LIFE_TIME';

PROFILE                        RESOURCE_NAME                    LIMIT
------------------------------ -------------------------------- ----------------------------------------
DEFAULT                        PASSWORD_LIFE_TIME               180

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Profile altered.

SQL> SELECT U.PROFILE, RESOURCE_NAME, LIMIT
  2  FROM DBA_USERS U, DBA_PROFILES P
  3  WHERE U.PROFILE=P.PROFILE
  4  AND USERNAME = 'SCOTT'
  5  AND RESOURCE_NAME='PASSWORD_LIFE_TIME';

PROFILE                        RESOURCE_NAME                    LIMIT
------------------------------ -------------------------------- ----------------------------------------
DEFAULT                        PASSWORD_LIFE_TIME               UNLIMITED

SQL>