最近比较忙,属于“吃的是草,挤的是奶”级别。之前写过一篇文章,可以通过user$查看用户密码修改时间[url]http://itspace.iteye.com/blog/421012[/url],同样在user$表中,记录了用户名密码不匹配的次数,可以看到LCOUNT初始值为0,ASTATUS为0表示用户处于unlock状态
[quote]SQL> select ASTATUS,LCOUNT from user$ where name='ZHOUL';
0 1[/quote]
继续尝试登陆
[quote]$ 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: [/quote]
尝试次数超过10次,可以看到ASTATUS变为4,即为LOCKED
[quote]SQL> select ASTATUS,LCOUNT from user$ where name='ZHOUL';
4 10
SQL> select ACCOUNT_STATUS from dba_users where username='ZHOUL';
LOCKED(TIMED)[/quote]
可以看到PROFILE 为DEFAULT
[quote]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
[color=red]FAILED_LOGIN_ATTEMPTS PASSWORD
10[/color]
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.[/quote]
再次登陆,即报ORA-28000错误
[quote]$ 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:[/quote]
执行手工解锁ASTATUS,LCOUNT置为初始值
[quote]SQL> alter user zhoul account unlock;
User altered.
SQL> select ASTATUS,LCOUNT from user$ where name='ZHOUL';
0 0[/quote]
参考文档
metalink:ID 752057.1 How We Resolved the Account Locked (Timed) issue in Oracle Database 10g
[quote]SQL> select ASTATUS,LCOUNT from user$ where name='ZHOUL';
0 1[/quote]
继续尝试登陆
[quote]$ 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: [/quote]
尝试次数超过10次,可以看到ASTATUS变为4,即为LOCKED
[quote]SQL> select ASTATUS,LCOUNT from user$ where name='ZHOUL';
4 10
SQL> select ACCOUNT_STATUS from dba_users where username='ZHOUL';
LOCKED(TIMED)[/quote]
可以看到PROFILE 为DEFAULT
[quote]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
[color=red]FAILED_LOGIN_ATTEMPTS PASSWORD
10[/color]
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.[/quote]
再次登陆,即报ORA-28000错误
[quote]$ 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:[/quote]
执行手工解锁ASTATUS,LCOUNT置为初始值
[quote]SQL> alter user zhoul account unlock;
User altered.
SQL> select ASTATUS,LCOUNT from user$ where name='ZHOUL';
0 0[/quote]
参考文档
metalink:ID 752057.1 How We Resolved the Account Locked (Timed) issue in Oracle Database 10g