How to cancel SQL statements in #Oracle 18c

A nice 18c New Feature is that you can cancel a certain SQL statement without using Resource Manager techniques. Here’s an example:

SQL> select banner_full from v$version;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0

SQL> select distinct sid from v$mystat;

     SID
---------
      477

SQL> begin loop null; end loop; end;   
     /

This produces an endless loop. Now I need the SQL_ID of the statement and the SERIAL# of the offending session:

SQL> select sql_text,sql_id from v$sql where sql_text like '%loop null; end loop%';

SQL_TEXT
--------------------------------------------------------------------------------
SQL_ID
-------------
begin loop null; end loop; end;
8gadd3yhtd150

select sql_text,sql_id from v$sql where sql_text like '%loop null; end loop%'
6vbb9d7zj9t5w


SQL> select serial# from v$session where sid=477;

   SERIAL#
----------
     10569

Now I can use the New Feature:

SQL> alter system cancel sql '477,10569,8gadd3yhtd150';

System altered.

The offending session gets

ERROR at line 1:
ORA-01013: user requested cancel of current operation

We waited for this functionality quite some time 🙂

,

  1. #1 by Luis Santos on April 5, 2018 - 22:25

    I tested and can assure: this is fully working on 12.2!

    I also tested on 11g and 12.1. Not working. It arrived (silently and not supported, but working) on 12.2.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: