How to Automate Killing Inactive Sessions with Resource Manager
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 channelMuch 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)
Share this
Next story
OEM 13c Monitoring Features - Part 1 →
You May Also Like
These Related Stories
Duplication: divide et impera
Duplication: divide et impera
Sep 19, 2013
9
min read
Backup Oracle databases to AWS S3
Backup Oracle databases to AWS S3
Feb 21, 2017
5
min read
Master/Slave Replication In Limited Bandwidth Scenarios
Master/Slave Replication In Limited Bandwidth Scenarios
Jan 23, 2012
3
min read
No Comments Yet
Let us know what you think