What are the reasons why Oracle users are locked?
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.
