Fault symptom
Oracle Database 11g Database common user login prompt ORA-28002: the password will expire within 7 days
[11: 01: 00oracle @ dvd db_1] $ sqlplus wang/oracle
SQL * Plus: Release 11.2.0.1.0 Production on Fri Nov 16 11:01:23 2012
Copyright (c) 1982,200 9, Oracle. All rights reserved.
ERROR:
ORA-28002: the password will expire within 7 days
Connected:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production
With the OLAP and Data Mining options
Wang @ SUN>
Fault Cause
Oracle 11G normal users have a default password cycle of 180 days. This prompt is displayed when the password expires;
Solution
View the profile and password cycle of the current user
Select * from dba_profiles where profile = 'default' AND resource_name = 'password _ LIFE_TIME ';
Alter profile default limit password_life_time unlimited;
SELECT username, profile from dba_users where username like upper ('& user ');
Sys @ SUN> SELECT username, profile from dba_users where username like upper ('& user ');
Enter value for user: wang
Old 1: SELECT username, profile from dba_users where username like upper ('& user ')
New 1: SELECT username, profile from dba_users where username like upper ('wang ')
USERNAME PROFILE
------------------------------------------------------------
WANG DEFAULT
-- The user's Profile file is found to be DEFAULT.
Sys @ SUN> set lines 222
Sys @ SUN> col PROFILE for a20
Sys @ SUN> col RESOURCE_NAME for a20
Sys @ SUN> col RESOURCE_TYPE for a20
Sys @ SUN> col LIMIT for a20
Sys @ SUN> select * from dba_profiles where profile = 'default' AND resource_name = 'password _ LIFE_TIME ';
PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
--------------------------------------------------------------------------------
DEFAULT PASSWORD_LIFE_TIME PASSWORD 180
-- The password lifecycle configured for this profile is 180 days.
Change the password cycle corresponding to the profile of the current user
Sys @ SUN> alter profile default limit password_life_time 365;
Profile altered.
-- Change the password lifecycle of the profile to 365 days.
Sys @ SUN> alter profile default limit password_life_time unlimited;
Profile altered.
-- Change the password lifecycle of this profile to unlimited