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 🙂