And here is the SQL manual entry for ALTER SYSTEM CANCEL SQL for those who want
to take a quick peek
https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/ALTER-SYSTEM.html#GUID-2C638517-D73A-41CA-9D8E-A62D1A0B7ADB
Mark Powell
Database Administration
(313) 592-5148
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Yong Huang <dmarc-noreply@xxxxxxxxxxxxx>
Sent: Thursday, May 3, 2018 10:03:42 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Long Running query alert and kill process
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
--
//www.freelists.org/webpage/oracle-l
