Causes of Oracle user lock and Solutions

The following articles mainly introduce the reason why Oracle users are locked and the actual solution. I saw the reason why Oracle users were locked and the actual solution materials on the relevant website two days ago, let's share it with you. I hope this will help you in your future studies.

The test user is notified of being locked during login.

1. log on to and unlock the account with the dba role. First, set the specific time format to view the specific time.

 
 

  
  
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';  
  
  
Session altered.  
 
 

2. view the lock time

 
 

  
  
SQL> select username,lock_date from dba_users where username='TEST';  
  
  
USERNAME LOCK_DATE  
  
  
TEST 2009-03-10 08:51:03  
 
 

3. Unlock

 
 

  
  
SQL> alter user test account unlock;  
  
  
User altered.  
 
 

4. Check that the test user is locked due to the ip address.

View $ ORACLE_HOME/network/admin/log/listener. log logs

10-MAR-2009 08:51:03 * (CONNECT_DATA = (SID = lhoms) (SERVER = DEDICATED) (CID = (PROGRAM = oracle) (HOST = omstestdb) (USER = oraoms ))) * (ADDRESS = (PROTOCOL = tcp) (HOST = 10.69.1.11) (PORT = 49434) * establish * lhoms * 0

10-MAR-2009 08:51:03 * (CONNECT_DATA = (SID = lhoms) (SERVER = DEDICATED) (CID = (PROGRAM = oracle) (HOST = omstestdb) (USER = oraoms ))) * (ADDRESS = (PROTOCOL = tcp) (HOST = 10.69.1.11) (PORT = 49435) * establish * lhoms * 0

It can be seen that the above ip address is locked due to multiple failed login attempts

Note:

By default, the database locks users after 10 failed attempts.

1. view the FAILED_LOGIN_ATTEMPTS Value

 
 

  
  
select * from dba_profiles 
 
 

2. Change to 30 times

 
 

  
  
alter profile default limit FAILED_LOGIN_ATTEMPTS 30; 
 
 

3. changing it to an unlimited number of times is not recommended for security reasons)

 
 

  
  
alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;   
 
 

The above content describes the cause and solution of Oracle user locks, hoping to help you in this regard.