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
and s.type <> 'BACKGROUND'
and last_call_et >= 30
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
1 of 1 people found this helpful
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.