##########################
## 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
