How to Automate Killing Inactive Sessions with Resource Manager

2 min read

Jan 6, 2020 12:00:00 AM

Are you manually (or via a script) killing idle sessions on your database? As a consequence, your users are getting error " ORA-00028: your session has been killed" and probably getting angry at you. What about doing it automatically, in a much more graceful way, and be seen as a nicer DBA? You can do this using Oracle Database Resource Manager (Resource Manager). Are you already using Resource Manager on your database? Yes? Great! No? Shame on you. Read this and put Resource Manager in place ASAP. This is a great tool for the database to manage the database resources, plus no additional licensing is needed. So go for it! Okay, but what's the catch? It's the limit max_idle_time. You can use it either on existing groups or subgroups of your plan (or subplan), or switch to "KILL" groups. With this, you can even use the same criteria you'd use for any script to perform this action. For this, you might use the parameter new_switch_group and create a different group only for those kills. Having this option is nicer if you want to avoid killing sessions on a database. By the way, you can always switch a session for the killing group manually, instead of demanding it fill the requirements of automatic switching. How? Quick example: Creating new Plan Groups with MAX_IDLE_TIME:

begin
 dbms_resource_manager.create_plan_directive( plan => '&RM_PLAN', group_or_subplan =>
 'LONG_RUN', comment => 'Limit idle time to 5 minutes', max_idle_time => 300);
 dbms_resource_manager.create_plan_directive( plan => '&RM_PLAN', group_or_subplan =>
 'SHORT_RUN', comment => 'Limit idle time to 1 minute', max_idle_time => 60);
 end;
 /

Cool! And what would be the error for the user that gets the session killed?

ORA-3113 End of file on communication channel

Much nicer: Now you are a nice DBA and don't kill sessions anymore. You automated it! Some additional recommendations:

Use this solution for Databases above 11.2.0.4 or 12.1.0.2, due to some known bugs:
- Bug 9523768 - IDLE SESSIONS AREN’T ACTUALLY KILLED IMMEDIATELY (affecting 11.2.0.1 to 11.2.0.3)
- Bug 13837378 - ALTER SYSTEM KILL SESSION IMMEDIATE DOES NOT KILL QC SESSIONS (11.2.0.1 to 12.1.0.1)
- Bug 8891495 - NON-IDLE SESSIONS ARE KILLED (11.2.0.1 only)
Some MOS references on it:
- How To Automatic Kill Inactive Sessions using Resource Manager (Doc ID 1935739.1)
- Using Resource Manager to Detect and Kill Idle Sessions (Doc ID 1557657.1)

Hope this helps! You can count on Pythian to help you in implementing this or any additional needs.

On this page

Ready to unlock value from your data?

With Pythian, you can accomplish your data transformation goals and more.

Speak with Pythian consultants now →

What We Do

How to Automate Killing Inactive Sessions with Resource Manager

Share this

Share this

More resources

Master/Slave Replication In Limited Bandwidth Scenarios

Case Study: How to Return a Good SQL Execution Plan from 10g Days After an 11g Migration

How to migrate from on-premises to Azure SQL database

Ready to unlock value from your data?