3 Replies Latest reply on Feb 20, 2015 12:55 PM by mandevil

    Way to edit LRT alert?


      Hi, I have alerts set up on some Oracle databases that are checking for Long Running Transactions, but I'm only seeing time values as being parameters to set. I would like to be able to say, "and owner not equal to <some schema>", etc. The problem is I have Streams set up one of these databases, and of course, I'm constantly getting alerts for strmsadmin and sys as long as Streams is up and running; therefore, I had to turn this alerting off for this database. Any ideas? Thanks

        • Re: Way to edit LRT alert?

          kill-9 (love the username!),

          Are you running this custom alert as in this page?  Custom Alert - Long Running Query (Oracle)

          If you are, you can simply add 'and username <> 'someschema' or other logic into the alert.


          Pretty simple statement:

          select 'SID:'||s.sid||', Serial#:'||s.serial#||', Username:'||s.username||', Machine:'||s.machine||

                 ', Program:'||s.program||', HashValue:'||s.sql_hash_value||', SQL Text:'||nvl(substr(sql.sql_text,1,40),'Unknown SQL'), last_call_et

          from v$session s

          left outer join v$sql sql on sql.hash_value=s.sql_hash_value and sql.address=s.sql_address

          where s.status='ACTIVE'

          and s.type <> 'BACKGROUND'

          and last_call_et >= 30

            • Re: Way to edit LRT alert?

              Okay thanks, I did eventually figure that out and it is working; but as a follow up question, why is "last_call_et >= 30"?  I would assume that is how long you want the query to have been running before getting an alert, so >= 30 means any query that runs for 30 seconds or longer, correct? So, if I only wanted to be alerted on queries running an hour or more, I would say ">=3600"? Or, do the min parm fields in the alert GUI override this and I can just enter the "3600" there?  --thanks again

                • Re: Way to edit LRT alert?

                  Just a way to limit results so that we're not parsing through every active session.  Think of it as a filter, not a determination of when you get notified..

                  You will set the threshold when you define the customer alert in the UI to be what threshold you want to get notified about.

                  1 of 1 people found this helpful