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

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

    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...

  • 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.
    --Creating plan directive
    exec dbms_resource_manager.create_plan_directive (
      comment=>'Limit idle time',
    Edited by: stefan nebesnak on Feb 4, 2013 11:49 AM


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