Hello All!
So, your are manually (or via script) killing idle sessions on your database?
As consequence, your users are getting error “ORA-00028: your session has been killed.” and getting angry on you?
What about doing it automatically in a much more graceful way and be seen as a nicer DBA? You can do it using Resource Manager.
Are you already using Resource Manager on your database? Yes – Great!
No – Shame on you. Read this and put RM 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!
Ok, but what is the trick?
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 user the parameter new_switch_group and have a created a different group only for those kills.
Have this option it’s nicer, if you want to avoid killing sessions on database, by the way, as you can always switch a session for the killing group manually, not demanding it to fill the requirements to 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 get’s 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 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 it helps!
