dba如何直接解决ORA-28002的问题?

发布时间:2019-11-24 00:00:00

问题:

登陆,就报口令还有7天就到期了,由于用户太多,有上1000个,能否不修改口令,通过dba统一修改?

下面模拟一下场景:

1.先确定一下环境:

db版本:

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production

PL/SQL Release 11.2.0.4.0 - Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

日期时间:

[oracle@dbserver ~]$ date

Thu Jan 9 18:03:39 CST 2014

口令到期时间:

SQL> select resource_name,limit from dba_profiles where profile='DEFAULT' and RESOURCE_NAME='PASSWORD_LIFE_TIME';

RESOURCE_NAME LIMIT

-------------------------------- ----------------------------------------

PASSWORD_LIFE_TIME 180

用户状态:

SQL> select username,ACCOUNT_STATUS,EXPIRY_DATE from dba_users where username='HR';

USERNAME ACCOUNT_STATUS EXPIRY_DA

------------------------------ -------------------------------- ---------

HR OPEN 08-JUL-14

通过以上,我们看到目前hr用户的到期时间为 2014-7-08号

SQL> select resource_name,limit from dba_profiles where profile='DEFAULT' and RESOURCE_NAME='PASSWORD_GRACE_TIME';

RESOURCE_NAME LIMIT

-------------------------------- ----------------------------------------

PASSWORD_GRACE_TIME 7

接下来,我们修改操作系统时间为7月9号

2.修改操作系统时间为7月9号

[root@dbserver ~]# date -s '2014-07-09 18:09'

Wed Jul 9 18:09:00 CST 2014

[root@dbserver ~]# date

Wed Jul 9 18:09:01 CST 2014

[root@dbserver ~]#

现在看看hr的状态

[oracle@dbserver ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 9 18:10:02 2014

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

SQL> connect / as sysdba

Connected.

SQL> select username,ACCOUNT_STATUS,EXPIRY_DATE from dba_users where username='HR';

USERNAME ACCOUNT_STATUS EXPIRY_DA

------------------------------ -------------------------------- ---------

HR OPEN 08-JUL-14

SQL>

大家看到 hr的到期时间还为 08-jul-14

3.登陆hr

SQL> conn hr/hr

ERROR:

ORA-28002: the password will expire within 7 days

Connected.

SQL>

一登陆,就提示还有7天就过期

看看状态

SQL> conn / as sysdba

Connected.

SQL> select username,ACCOUNT_STATUS,EXPIRY_DATE from dba_users where username='HR';

USERNAME ACCOUNT_STATUS EXPIRY_DA

------------------------------ -------------------------------- ---------

HR EXPIRED(GRACE) 16-JUL-14

SQL>

可以看到,这个account状态,只有在登陆时,才会变化.不登陆,就不会变化.

大家看到,hr的用户的状态已经变成 expired(grace)状态了

在7天以内hr登陆,都会提示这个 ora-28002的提示,不影响使用,但到 16-JUL-14 后,如果口令还不到期,这个用户就要被锁定


<<