Cancelando a execução de uma instrução SQL

Cancelando a execução de uma instrução SQL

Uma das novas features introduzidas na versão 18c do Oracle database é o ALTER SYSTEM CANCEL SQL que permite que uma instrução SQL em execução em determinada sessão seja manualmente cancelada sem que a sessão seja desconectada, o que ocorre na utilização das instruções ALTER SYSTEM KILL SESSION / DISCONNECT SESSION.

A utilização do “CANCEL SQL” é útil quando determinada sessão está executando alguma instrução ofensora e é necessário liberação de recursos ou mesmo quando instruções SQL são executadas incorretamente.

Sempre que uma instrução SQL é cancelada, ocorre o rollback dos dados alterados pela mesma.

Sintaxe do comando:

ALTER SYSTEM CANCEL SQL 'SID, SERIAL#, [@INST_ID, SQL_ID]';

Caso a @INST_ID não seja especificada (opcional), a instance id da sessão conectada será utilizada;

Caso o SQL_ID não seja especificado (opcional), a instrução SQL atualmente em execução na sessão especificada será cancelada.

-- Cancelar SQL em execução em determinada sessão da instance conectada.

SQL> ALTER SYSTEM CANCEL SQL '38, 41388';

-- Cancelar SQL em execução na sessão com INST_ID = 1.

SQL> ALTER SYSTEM CANCEL SQL '38, 41388, @1';

-- Cancelar SQL específico em determinada sessão da instance conectada.

SQL> ALTER SYSTEM CANCEL SQL '38, 41388, 3tfmdd4xagv3y';

-- Cancelar SQL específico na sessão com INST_ID = 1.

SQL> ALTER SYSTEM CANCEL SQL '38, 41388, @1, 3tfmdd4xagv3y';

Exemplo:

Connected to: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
----------
38

SQL> begin
  2 loop null;
  3 end loop;
  4 end;
  5 /

Em uma nova sessão, capture as informações necessárias e execute o ALTER SYSTEM CANCEL SQL:

SQL> set lines 200

SQL> select inst_id, serial#, sql_id, status, (select sql_fulltext from v$sql sq where sq.sql_id=se.sql_id) sql_fulltext from gv$session se where sid=38;

   INST_ID    SERIAL# SQL_ID        STATUS SQL_FULLTEXT
---------- ---------- ------------- -------- ------------------------------
         1 41388 3tfmdd4xagv3y ACTIVE   begin
                                             loop null;
                                             end loop;
                                             end;

SQL> ALTER SYSTEM CANCEL SQL '38, 41388, @1, 3tfmdd4xagv3y';

System altered.

Voltando a sessão anterior:

SQL> begin
  2 loop null;
  3 end loop;
  4 end;
  5 /

begin
*
ERROR at line 1:

ORA-01013: user requested cancel of current operation

E a sessão continua conectada, basta executar qualquer instrução SQL.

SQL> select distinct sid from v$mystat;

SID
----------
38

Referência

• New Features