Library Cache Locks Due to Invalid Login Attempts:

Slution :

SQL> select * from dba_ddl_locks where mode_held='Exclusive';

1. Check for bad or incorrect password or login attack by running following sql:

SQL> select username,

os_username,

userhost,

client_id,

trunc(timestamp),

count(*) failed_logins

from dba_audit_trail

where returncode=1017 and --1017 is invalid username/password

timestamp < sysdate -7

group by username,os_username,userhost, client_id,trunc(timestamp);

 

2. Set the below event in the spfile or init.ora file and restart the database:

SQL> alter system set event ="28401 TRACE NAME CONTEXT FOREVER, LEVEL 1" scope=spfile;

or

EVENT="28401 TRACE NAME CONTEXT FOREVER, LEVEL 1"

Note:

Care should be taken when setting this event, as this is disabling the sleep time which can leave the system more vulnerable to brute force password guessing attacks.

For full information on this issue please read the following note before deciding to set the event:

Document:7715339.8 Logon failures causes "row cache lock" waits - Allow disable of logon delay

In 11.1.0.7, if One off fix Patch 7715339 is applied, the delay is disabled  unconditionally, and there is no need to set the event.

NOTE: This issue is fixed in  :11.2.0.1 (Base Release)

++++++++++(Doc ID 1309738.1)+++++++++++++