ORACLE11g错误密码登录10次将被锁定

ORACLE11g错误密码登录10次将被锁定，测试如下：

SQL> set time on;
15:32:18 SQL> conn gxgat/password
Connected.
15:32:29 SQL> conn gxgat/p
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
15:32:42 SQL> conn gxgat/p
ERROR:
ORA-01017: invalid username/password; logon denied


15:32:48 SQL> conn gxgat/p
ERROR:
ORA-01017: invalid username/password; logon denied


15:32:57 SQL> conn gxgat/p
ERROR:
ORA-01017: invalid username/password; logon denied


15:33:06 SQL> conn gxgat/p
ERROR:
ORA-01017: invalid username/password; logon denied


15:33:11 SQL> conn gxgat/p
ERROR:
ORA-01017: invalid username/password; logon denied


15:33:17 SQL> conn gxgat/p
ERROR:
ORA-01017: invalid username/password; logon denied


15:33:27 SQL> conn gxgat/p
ERROR:
ORA-01017: invalid username/password; logon denied


15:33:38 SQL> conn gxgat/p
ERROR:
ORA-01017: invalid username/password; logon denied


15:33:50 SQL> conn gxgat/p
ERROR:
ORA-01017: invalid username/password; logon denied


15:33:59 SQL> conn gxgat/p
ERROR:
ORA-28000: the account is locked

解锁：

先退出，以sysdba进行登录，执行unlock命令即可。

[oracle@test11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 20 15:39:53 2018

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter user gxgat account unlock;

User altered.

SQL> conn gxgat/password
Connected.

查看默认错误连接次数锁定：

SQL> set linesize 300
SQL> select * from dba_profiles where resource_name like 'FAILED_LOGIN_ATTEMPTS%';

PROFILE 		       RESOURCE_NAME			RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT 		       FAILED_LOGIN_ATTEMPTS		PASSWORD 10
MONITORING_PROFILE	       FAILED_LOGIN_ATTEMPTS		PASSWORD UNLIMITED

SQL> 

修改默认的错误登录次数，为不限制

SQL> alter profile DEFAULT limit FAILED_LOGIN_ATTEMPTS UNLIMITED;

查看被锁的用户

15:51:48 SQL> select LOCK_DATE,username from dba_users where username='GXGAT';

LOCK_DATE USERNAME
--------- ------------------------------
20-DEC-18 GXGAT

LOCK_DATE为空说明没有锁定，非空为锁定。

或者

SELECT S.USERNAME,
       DECODE(L.TYPE, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL,
       O.OWNER,
       O.OBJECT_NAME,
       O.OBJECT_TYPE,
       S.SID,
       S.SERIAL#,
       S.TERMINAL,
       S.MACHINE,
       S.PROGRAM,
       S.OSUSER
   FROM V$SESSION S, V$LOCK L, DBA_OBJECTS O
WHERE S.SID = L.SID
   AND O.OBJECT_ID = L.ID1
   AND S.USERNAME IS NOT NULL;