Oracle 用户密码不匹配导致用户锁住

最近比较忙，属于“吃的是草，挤的是奶”级别。之前写过一篇文章，可以通过user$查看用户密码修改时间http://itspace.iteye.com/blog/421012,同样在user$表中，记录了用户名密码不匹配的次数，可以看到LCOUNT初始值为0，ASTATUS为0表示用户处于unlock状态

引用

SQL> select ASTATUS,LCOUNT from user$ where name='ZHOUL';
         0          1

继续尝试登陆

引用

$ sqlplus "zhoul/zhou"

SQL*Plus: Release 10.2.0.4.0 - Production on Sat May 8 01:21:19 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:

尝试次数超过10次，可以看到ASTATUS变为4，即为LOCKED

引用

SQL> select ASTATUS,LCOUNT from user$ where name='ZHOUL';
         4         10
SQL> select ACCOUNT_STATUS from dba_users where username='ZHOUL';
LOCKED(TIMED)

可以看到PROFILE  为DEFAULT

引用

SQL> select ACCOUNT_STATUS,PROFILE  from dba_users where username='ZHOUL';
OPEN                             DEFAULT

SQL> SELECT resource_name,resource_type,limit FROM dba_profiles WHERE profile='DEFAULT';
COMPOSITE_LIMIT                  KERNEL
UNLIMITED

SESSIONS_PER_USER                KERNEL
UNLIMITED

CPU_PER_SESSION                  KERNEL
UNLIMITED

CPU_PER_CALL                     KERNEL
UNLIMITED

LOGICAL_READS_PER_SESSION        KERNEL
UNLIMITED

LOGICAL_READS_PER_CALL           KERNEL
UNLIMITED

IDLE_TIME                        KERNEL
UNLIMITED

CONNECT_TIME                     KERNEL
UNLIMITED

PRIVATE_SGA                      KERNEL
UNLIMITED

FAILED_LOGIN_ATTEMPTS            PASSWORD
10

PASSWORD_LIFE_TIME               PASSWORD
UNLIMITED

PASSWORD_REUSE_TIME              PASSWORD
UNLIMITED

PASSWORD_REUSE_MAX               PASSWORD
UNLIMITED

PASSWORD_VERIFY_FUNCTION         PASSWORD
NULL

PASSWORD_LOCK_TIME               PASSWORD
UNLIMITED

PASSWORD_GRACE_TIME              PASSWORD
UNLIMITED


16 rows selected.

再次登陆，即报ORA-28000错误

引用

$ sqlplus "zhoul/zhoul"

SQL*Plus: Release 10.2.0.4.0 - Production on Sat May 8 01:26:20 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:
ORA-28000: the account is locked


Enter user-name:

执行手工解锁ASTATUS,LCOUNT置为初始值

引用

SQL> alter user zhoul account unlock;

User altered.

SQL> select ASTATUS,LCOUNT from user$ where name='ZHOUL';
         0          0

参考文档
metalink：ID 752057.1 How We Resolved the Account Locked (Timed) issue in Oracle Database 10g