Automatically Killing Idle Blocking Sessions in Oracle 19c & 20c

Leave a reply

A Simple but Powerful DBA Tip

Blocking sessions are one of the most common and frustrating issues Oracle DBAs face. A user starts a transaction, locks rows, and then… goes for lunch ☕. Meanwhile, the entire application waits.

Starting with Oracle Database 19c and 20c, Oracle introduced a very useful initialization parameter that helps DBAs handle this problem automatically.

The Problem: Idle Blocking Sessions

An idle blocking session is a session that:

  • Holds locks on rows or objects
  • Is not actively executing any SQL
  • Prevents other sessions from proceeding

Typical causes:

  • Users forgetting to commit or rollback
  • Application sessions left open
  • Manual updates run and abandoned

Until recently, DBAs had to:

  • Manually identify blocking sessions
  • Kill them using ALTER SYSTEM KILL SESSION
  • Or rely on application discipline

The Solution: 

MAX_IDLE_BLOCKER_TIME

Oracle 19c and 20c introduced the initialization parameter:

MAX_IDLE_BLOCKER_TIME

What Does It Do?

It automatically terminates a session that:

  • Is blocking other sessions
  • Has been idle for more than the specified time

⏱️ The time is specified in minutes.

Once the threshold is reached, Oracle kills the session automatically.

How It Works (Internally)

Oracle monitors sessions and checks:

  1. Is the session blocking others?
  2. Is the session idle (no SQL execution)?
  3. Has it exceeded the configured idle time?

If all three are true, Oracle terminates the session safely.

Example Configuration

Set at System Level

ALTER SYSTEM SET MAX_IDLE_BLOCKER_TIME = 15;

✔️ Any blocking session idle for more than 15 minutes will be killed.

Check Current Value

SHOW PARAMETER MAX_IDLE_BLOCKER_TIME;

Practical Scenario

Without 

MAX_IDLE_BLOCKER_TIME

  • User updates rows in a table
  • Does not commit
  • Leaves session idle
  • Other users are blocked indefinitely
  • DBA receives urgent calls 😓

With 

MAX_IDLE_BLOCKER_TIME = 10

  • User blocks rows
  • Session stays idle for 10 minutes
  • Oracle terminates the session automatically
  • Locks are released
  • Application continues smoothly ✅

Important Notes & Best Practices

✔️ Applies only to blocking sessions

✔️ Idle but non-blocking sessions are not affected

✔️ Works well in OLTP environments

✔️ Choose a value carefully (10–30 minutes is common)

⚠️ Do not set it too low in environments with:

  • Long user think time
  • Manual batch operations
  • Legacy applications

What About Older Oracle Versions?

This exact parameter is only available in 19c and 20c.

However, similar behavior can be implemented in older versions using:

  • Oracle Resource Manager
  • Idle time and session control directives

A great explanation of this approach is provided by Connor McDonald, who explains how Resource Manager can be used to handle idle blocking sessions effectively.

Why Every DBA Should Use This

✔️ Reduces production incidents

✔️ Prevents long blocking chains

✔️ Saves DBA intervention time

✔️ Improves application availability

This small parameter can eliminate a huge class of locking problems in Oracle databases.

Final Thoughts

Oracle continues to add practical, real-world DBA features in modern releases.

MAX_IDLE_BLOCKER_TIME is one such hidden gem that every Oracle DBA should strongly consider enabling.

If you manage Oracle 19c or 20c databases and still rely on manual session killing — this parameter is a must-have.