3 Replies Latest reply: Feb 4, 2013 1:50 PM by stefan nebesnak RSS

    kill blocker automatically

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