7 Replies Latest reply: Apr 18, 2013 2:46 AM by Azmat RSS

    Script to Kill long running queries

    Azmat
      Hi Friends,

      Would be obliged if someone can help me with the following required script.

      I need a script please which kills any session recieved from a certain user (i.e userA) which stays on the DB for more then 5 minutes. if a query has been executing for more then 5 minutes it should be killed by the script.

      Any help would be highly appreciated my friends :)

      regards
      Azmat
        • 1. Re: Script to Kill long running queries
          Srini Chavali-Oracle
          Pl post exact OS and database versions. Read the documentation on using DBA_PROFILES - no script is necessary

          http://docs.oracle.com/cd/E11882_01/network.112/e16543/users.htm#BABCIJAI
          http://docs.oracle.com/cd/E11882_01/server.112/e25513/statviews_4185.htm#REFRN23169

          HTH
          Srini
          • 2. Re: Script to Kill long running queries
            Azmat
            Hi Srini,

            Accept my apology for missing the basics :(

            OS : SunOS gigagram1 5.10 Generic_142900-11 sun4u sparc SUNW,SPARC-Enterprise
            DB : SQL*Plus: Release 10.2.0.4.0 - Production on Sun Dec 2 19:04:27 2012

            Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


            In the meantime i am going through your provided documents also.
            • 3. Re: Script to Kill long running queries
              Srini Chavali-Oracle
              No apology is needed :-) Versions are requested because options/choices vary by version - and a solution not appropriate or available for your specific version would not be of much help.

              Pl see these links for 10.2.0.4

              http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_4033.htm#sthref1758
              http://docs.oracle.com/cd/B19306_01/server.102/b14220/security.htm#sthref2740
              http://docs.oracle.com/cd/B19306_01/network.102/b14266/authoriz.htm#i1009631

              HTH
              Srini
              • 4. Re: Script to Kill long running queries
                vlethakula
                Do you want to kill the query or session?
                or do you want the user to be logged out after 5 mts although they are not running any intensive queries?
                • 5. Re: Script to Kill long running queries
                  Azmat
                  Hi Vlethakula

                  I would like to kill the query please which is run by the said user.

                  Regards
                  • 6. Re: Script to Kill long running queries
                    Billy~Verreynne
                    Azmat wrote:

                    I would like to kill the query please which is run by the said user.
                    Then why not simply deny that "bad" user access to the database for running "bad" queries? ;-)

                    Killing long running queries is treating thye symptom. Not the problem. The user is firing off that query to get some kind of answer to a question. You killing that query does not answer that question. Which means the user is likely simply going to fire it off again.

                    It could be that the SQL query the user has put together is less than optimal in finding the answer. It could be that the question is ambiguous and not well framed ito the data model and data available. It could be that the SQL is optimally designed, but the data volumes are simply very large. Etc and etc.

                    So rather ask these questions to determine WHAT and WHY the user is executing long running queries and address these. Killing that user's sessions is not any kind of solution. Automating the killing of such sessions are even less of an answer and is highly questionable as just wtf is going on, on that database?
                    • 7. Re: Script to Kill long running queries
                      Azmat
                      No solution provided....so closing it but thanks anyways