Solution : https://service.sap.com/sap/support/notes/20071 (SAP Service marketplace login required)
Summary :
SAP work processes face serialization when accessing a specific table in the R/3 system, leading to Oracle session hangs confirmed by persistent wait events like enqueues or latches in V$SESSION_WAIT. DDL operations without the ONLINE option fail due to ORA-00054 errors, indicating locked resources. Hang-ups are due to Oracle sessions that remain active, awaiting input from non-existent SAP client processes, a scenario possibly provoked by an underlying network issue. To rectify this, one can restart the Oracle database, adjust SQLNET.EXPIRE_TIME in sqlnet.ora, configure TCP/IP for session timeouts on Windows, or manually terminate the blocking session to resolve or preempt such lockups.
Key words :
enqueue library cache lock latch free row cache lock ddl statements, hkey_local_machine\system\currentcontrolset\services\tcpip\parameters, library cache lock note 607944 describes, cancelled long-running sql statements, row cache lock determine, alter system kill session '<sid>, unnecessarily long time passes, row cache locks, tcp/ip timeout helps, latch free determine
Related Notes :
| 767414 | FAQ: Oracle latches |
| 745639 | FAQ: Oracle enqueues |
| 619188 | FAQ: Oracle wait events |
| 618868 | FAQ: Oracle performance |
| 618861 | Composite SAP Note: ORA-01013 |
| 607944 | library cache lock: Identify and eliminate the lockholder |