Re: Long Running query alert and kill process
From: Yong Huang <"Yong>
Date: Thu, 3 May 2018 14:03:42 +0000 (UTC)
Message-ID: <1222775807.3693976.1525356222185_at_mail.yahoo.com>
Date: Thu, 3 May 2018 14:03:42 +0000 (UTC)
Message-ID: <1222775807.3693976.1525356222185_at_mail.yahoo.com>
Sanjay,
Not directly answering your question. In Oracle 18c, you can cancel the long running query instead of killing the session:
https://docs.oracle.com/en/database/oracle/oracle-database/18/newft/new-features.html#GUID-49F77A8D-CF54-415C-B04F-DB4CE733C513
Manual termination of run-away queries
A SQL query consuming excessive resources, including parallel servers, can be terminated using the ALTER SYSTEM CANCEL SQL statement.
As to monitoring a long-running query, I have a cron job that checks v$session_longops (joined to v$session) for elapsed_seconds. The job sends an email to me instead of killing the session. I'm sure OEM can do this too.
Yong Huang
-- http://www.freelists.org/webpage/oracle-lReceived on Thu May 03 2018 - 16:03:42 CEST