A 42-minute inactive blocker, plus ITL waits on another table

'enq: TX - row lock contention' means a session wants to modify a row that another transaction has locked. The holder is SID 145, which is INACTIVE: the client is not executing anything, but its transaction is open and its row lock is intact. Nothing in the database will clear it except a commit, rollback, or session kill. The fix is at the transaction level.

'enq: TX - allocate ITL entry' is different: the block is contended, not the row. Every block header has an Interested Transaction List with slots (INITRANS). Session 301's block has all slots in use by concurrent transactions, so it must wait for one to commit or for a slot to be cleaned out. Causes are genuine concurrency on the same block (too few INITRANS for the design) or a long-running transaction squatting in a slot.

Because they are different resources, remedies differ: row-lock waits need transaction discipline or a kill; ITL waits need INITRANS raised on the hot segment (rebuild/move to take effect) and a review of whether rows clustering on few blocks is the real design problem.

Check first:

• Is it a prepared/in-doubt distributed transaction? DBA_2PC_PENDING is empty here, which rules out the case where a kill does not release the lock and you must ROLLBACK FORCE the local transaction.
• What else does the transaction hold? Killing rolls the whole transaction back, including work the user may still want. Query v$lock for SID 145 and v$transaction for its undo usage to estimate rollback time.
• Is APP04's pooled connection actually dead? An INACTIVE session with a stale last call is typically a leaked or orphaned pool connection; if the pool still believes it owns the session, killing it will make the pool's next use fail and it should be bounced/recycled too. Coordinate with the app owner - otherwise the same thing recurs in an hour.

Then ALTER SYSTEM KILL SESSION '145,<serial#>';. This is asynchronous: the session is marked KILLED, PMON rolls back the transaction, and the locks release only when rollback finishes - a long transaction means a visible delay. Do not use kill -9 on the OS process; it bypasses PMON's cleanup path and can leave the instance with an unreleased lock or require a restart.

For row locks:

• Find the pattern: a UI that opens a transaction on a user action and commits only on a later screen is the usual culprit. Monitor v$lock/v$session for blockers older than a threshold and alert.
• A profile with a finite IDLE_TIME bounds how long an idle session - and therefore its open transaction - can persist, at the cost of killing legitimately idle users. It is a blunt but effective backstop for pooled connections.
• Ensure the connection pool validates and recycles connections; a leaked connection is an application defect that no database setting fully cures.

For ITL:

• Recreate or move ORDERS_LINES with a higher INITRANS (e.g. 8–16) sized for expected concurrent transactions per block. PCTFREE also matters: if the table is packed, concurrent rows share few blocks and collide.
• Confirm after the change by watching the wait event disappear from v$active_session_history during peak.

Do not 'fix' any of this by increasing undo or by raising DML_LOCKS; neither addresses the wait events seen here.