This discussion is archived
3 Replies Latest reply: Feb 4, 2013 11:50 AM by stefan nebesnak RSS

kill blocker automatically

Ora_83 Newbie
Currently Being Moderated
Hi


Is there a way to automate killing blocking session in oracle ?

i.e if a session is blocking more than 5 minutes, it should be killed automatically.

Do we need a trigger to achieve this ?
  • 1. Re: kill blocker automatically
    odie_63 Guru
    Currently Being Moderated
    I think a better question would be : "why do I ever need to do that?"

    What problem are you trying to address? Why do you have blocking sessions and why are they problematic?
  • 2. Re: kill blocker automatically
    asahide Expert
    Currently Being Moderated
    Hi,

    In our experience, automate killing will cause various problems.

    Anyway, I have implemented it by Shell script.
    Check V$LOCK & V$SESSION -> alter system kill session...


    Regards,
  • 3. Re: kill blocker automatically
    stefan nebesnak Journeyer
    Currently Being Moderated
    See MAX_IDLE_BLOCKER_TIME (dbms_resource_manager.create_plan_directive) - the maximum amount of time in seconds that a session can be idle while blocking another session's acquisition of a resource. Default is NULL, which implies unlimited.

    http://docs.oracle.com/cd/E14072_01/server.112/e10595/dbrm003.htm
    --Creating plan directive
    
    exec dbms_resource_manager.create_plan_directive (
      plan=>'prod_plan',
      group_or_subgroup=>'dss_group',
      comment=>'Limit idle time',
      max_idle_time=>900,
    max_idle_blocker_time=>300);
    
    --http://www.datadisk.co.uk/html_docs/oracle/resource_manager.htm
    Edited by: stefan nebesnak on Feb 4, 2013 11:49 AM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points