cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Highlighted
Level 8

Way to edit LRT alert?

Jump to solution

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

Labels (1)
0 Kudos
1 Solution

Accepted Solutions
Highlighted
Level 14

Re: Way to edit LRT alert?

Jump to solution

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

View solution in original post

0 Kudos
3 Replies
Highlighted
Level 14

Re: Way to edit LRT alert?

Jump to solution

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

View solution in original post

0 Kudos
Highlighted
Level 8

Re: Way to edit LRT alert?

Jump to solution

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

0 Kudos
Highlighted
Level 14

Re: Way to edit LRT alert?

Jump to solution

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.