Oracle数据库自动被锁定的解决方法a

不知道为什么最近在使用的一个数据库总是在一段时间后被锁住。

google一下，问题好像是用错误的密码登录超过10次就会导致账户自动被锁。

解决的根本方法是找到是哪里在使用错误密码不断尝试登录

但是如果你一时之间找不到问题根本：

可以采取如下方法：

1.使用dba账户登录：

Login as SYSDBA

SQL> conn /as sysdba

2.检查账户的状态

Check the APPSUSR account status.

SQL> SELECT username, account_status FROM dba_users WHERE username= ‘APPUSR';

 

USERNAME ACCOUNT_STATUS PROFILE
-------------------- -------------------- ---------------
APPUSR LOCKED(TIMED) DEFAULT

检查Default profile的资源限制

Check the resource limits of DEFAULT profile.

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

RESOURCE_NAME RESOURCE LIMIT
-------------------------------- -------- ----------
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

 

这里不建议直接修改Default profile，最好创建一个新的profile

Create a profile.

 

SQL> CREATE PROFILE APPUSR_DEFAULT LIMIT
2 COMPOSITE_LIMIT UNLIMITED
3 SESSIONS_PER_USER UNLIMITED
4 CPU_PER_SESSION UNLIMITED
5 CPU_PER_CALL UNLIMITED
6 LOGICAL_READS_PER_SESSION UNLIMITED
7 LOGICAL_READS_PER_CALL UNLIMITED
8 IDLE_TIME UNLIMITED
9 CONNECT_TIME UNLIMITED
10 PRIVATE_SGA UNLIMITED
11 FAILED_LOGIN_ATTEMPTS UNLIMITED
12 PASSWORD_LIFE_TIME UNLIMITED
13 PASSWORD_REUSE_TIME UNLIMITED
14 PASSWORD_REUSE_MAX UNLIMITED
15 PASSWORD_VERIFY_FUNCTION NULL
16 PASSWORD_LOCK_TIME UNLIMITED
17 PASSWORD_GRACE_TIME UNLIMITED;

Profile created.

将新的proflie设置为默认的profile

Assign the newly created profile to the user as default profile.

SQL> ALTER USER appusr PROFILE appusr_default;

User altered.

 

解锁账户

Unlock the user account:

SQL> ALTER USER appusr ACCOUNT UNLOCK;

User altered.

重新检查账户状态
Now check again the status of APPUSR user.

SQL> SELECT username, account_status FROM dba_users WHERE username= ‘APPUSR';
USERNAME ACCOUNT_STATUS PROFILE
-------------------- -------------------- ---------------
APPUSR OPEN APPUSR_DEFAULT

至此，问题解决。