Shulou(Shulou.com)06/01 Report--

This article will explain in detail what causes Oracle users to be locked. Xiaobian thinks it is quite practical, so share it with you for reference. I hope you can gain something after reading this article.

Oracle users locked reasons and solutions

Tells test user is locked when logging in

1. Log in with the user of the dba role to unlock it. Set the specific time format first to view the specific time.

SQL> alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss';

Session altered.

2. Check the specific locked 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 which ip caused the test user to be locked

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

This shows that the IP of 10.69.1.11 above has been locked due to multiple failed login attempts.

Note:

General database default is to lock the user after 10 failed attempts

1. Check the value of FAILED_LOGIN_ATTEMPTS

select * from dba_profiles

2. Modified to 30 times

alter profile default limit FAILED_LOGIN_ATTEMPTS 30;

3. Modify to unlimited times (for safety reasons, it is not recommended to use)

alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;

About "Oracle users are locked what are the reasons" this article is shared here, I hope the above content can be of some help to everyone, so that you can learn more knowledge, if you think the article is good, please share it to let more people see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.