Automatic killing of dead lock sessions

If you are experiencing frequent dead lock problem in your database and want to kill automatically causing sessions. You can accomplished this task with Oracle Resource Manager.

It will kill the sessions those are waiting /hanging more than 20 minutes due to dead lock but you can change minutes as per your requirement. There are 2 groups and this package will be implemented on first group "all_dev_users" and 2^nd group "other_groups" will be not effected this package.

BEGIN

Dbms_Resource_Manager.Clear_Pending_Area();

Dbms_Resource_Manager.Create_Pending_Area();

-- Create plan
--============================
Dbms_Resource_Manager.Create_Plan(
plan => 'kill_blocking_idle_session',
comment => 'Plan to kill blocking idle sessions.');

-- Create consumer groups
--============================
Dbms_Resource_Manager.Create_Consumer_Group(
consumer_group => 'all_dev_users',
comment => 'for all dev10g users');


-- Assign consumer groups to plan and define priorities
--============================
Dbms_Resource_Manager.Create_Plan_Directive (
plan => 'kill_blocking_idle_session',
group_or_subplan => 'all_dev_users',
comment => '20 mins killed blocking idle sessions',
max_idle_blocker_time => 1200);

-Change minutes as per your requirement

Dbms_Resource_Manager.Create_Plan_Directive(
plan => 'kill_blocking_idle_session',
group_or_subplan => 'OTHER_GROUPS',
comment => 'all other users' );
-- Users belong to group 'OTHER_GROUPS' will not be killed.

Dbms_Resource_Manager.Validate_Pending_Area;

Dbms_Resource_Manager.Submit_Pending_Area();

END;
/


BEGIN
-- Assign users to consumer groups

Dbms_Resource_Manager_Privs.Grant_Switch_Consumer_Group(
grantee_name => 'FAMI_API',
consumer_group => 'all_dev_users',
grant_option => FALSE);

Dbms_Resource_Manager.Set_Initial_Consumer_Group('FAMI_API', 'all_dev_users');
END;
/

--To enable the resource plan kill_blocking_idle_session

--==========================================
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = kill_blocking_idle_session scope=both;

--To know how many has been killed since last startup of database

--================================================

select id, NAME, IDLE_BLKR_SESSIONS_KILLED from v$RSRC_CONSUMER_GROUP;