ORA-28002 Oracle 11g存在密码过期问题解决方案

时间:2021-10-08 05:51:42

故障现象 
Oracle Database 11g 数据库普通用户登录时提示 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, 2009, 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.1.0 - Production 
With the OLAP and Data Mining options 
wang@SUN> 

故障原因 
Oracle 11G 普通用户有个180天的缺省密码周期,当快过期的时候即会出现此提示; 

解决办法 
查看当前用户对应profile、对应密码周期 
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 
--查询到该用户对应的Profile文件为 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 
--查询到该profile对应的密码生命周期配置为180天 
修改当前用户对应profile对应对应密码周期 
sys@SUN> alter profile default limit password_life_time 365; 
Profile altered. 
--修改该profile对应的密码生命周期配置为365天 
sys@SUN> alter profile default limit password_life_time unlimited; 
Profile altered. 
--修改该profile对应的密码生命周期配置为 无限制