Unlock database users and database users

Unlock database users and database users

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. Check the lock time. (here, you need to enter the specific user name. The user name in the following statement is TEST)
SQL> select username, lock_date from dba_users where username = 'test ';
USERNAME LOCK_DATE
TEST 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. Change to unlimited (for security reasons, it is not recommended)
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.