Friday, August 30, 2013

ORA-28002: the password will expire within 7 days


##########################
## Error
##########################

ORA-28002: the password will expire within 7 days

### Full Error

oralin_2 @ hostp03:/etc
> sqlplus system/pass_word@oralin_gvl

SQL*Plus: Release 11.2.0.2.0 Production on Fri Aug 30 06:14:48 2013

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

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL>

##########################
## Cause
##########################

Error occured while trying to connect using a user.

##########################
## Command Executed
##########################

conn system/pass

##########################
## Solution
##########################

###

Check User Account Status :

 select username,account_status,expiry_date,profile from dba_users where username='SYSTEM';

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DA PROFILE
------------------------------ -------------------------------- --------- ------------------------------
SYSTEM                         EXPIRED(GRACE)                   06-SEP-13 DEFAULT


Check the Profile limit set for password life time

select * from dba_profiles where RESOURCE_NAME='PASSWORD_LIFE_TIME';

PROFILE                                  RESOURCE_NAME                  RESOURCE LIMIT
---------------------------------------- ------------------------------ -------- -------------------------
DEFAULT                                  PASSWORD_LIFE_TIME             PASSWORD 180

If you dont want to alter the profile limit set, then change the password for the user by noting it values from user$ and then reset it back.

If you wanna change the profile limit then,

Alter profile default limit password_life_time unlimited;

SQL> Alter profile default limit password_life_time unlimited;

Profile altered.

select * from dba_profiles where RESOURCE_NAME='PASSWORD_LIFE_TIME';

PROFILE                                  RESOURCE_NAME                  RESOURCE LIMIT
---------------------------------------- ------------------------------ -------- -------------------------
DEFAULT                                  PASSWORD_LIFE_TIME             PASSWORD UNLIMITED

After changing the profile limit also the user account will be in expired status only,

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DA PROFILE
------------------------------ -------------------------------- --------- ----------------------------------------
SYSTEM                         EXPIRED(GRACE)                   06-SEP-13 DEFAULT

Now change the password you would like or use the same password to set.

Alter user system identified by pass_word;

SQL> Alter user system identified by pass_word;

User altered.

SQL> select username,account_status,expiry_date,profile from dba_users where username='SYSTEM';


USERNAME                       ACCOUNT_STATUS                   EXPIRY_DA PROFILE
------------------------------ -------------------------------- --------- ----------------------------------------
SYSTEM                         OPEN                                       DEFAULT

No comments: