Re: Long Running query alert and kill process

On Linux, you can cancel the current SQL by sending SIGURG (23) to the executing process. I believe that is an equivalent available to those few among us who are still not running 18c. On Windows, there is a procedure with a sledgehammer which requires physical proximity to the machine. Looks like this:

https://www.youtube.com/watch?v=R_FoFbAyVQk

That will cancel any query running on the Windows server.

On 05/03/2018 03:43 PM, Powell, Mark wrote:
>
> 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_at_freelists.org <oracle-l-bounce_at_freelists.org>
> on behalf of Yong Huang <dmarc-noreply_at_freelists.org>
> *Sent:* Thursday, May 3, 2018 10:03:42 AM
> *To:* oracle-l_at_freelists.org
> *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
> --
> http://www.freelists.org/webpage/oracle-l
>
>

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217


--
http://www.freelists.org/webpage/oracle-l