Script para cancelar SQLs de um usuário específico no Oracle Database

Olá pessoal,

    No post de hoje vou compartilhar um script que pode ser útil para muitos DBAs que administram Oracle Database versão 12c ou superior. A partir dessa versão é possível cancelar o SQL de uma sessão, e isso é muito útil em diversas ocasiões que irei comentar a seguir.

    Quem nunca passou por uma situação em que uma nova aplicação foi implantada, ou uma aplicação existente foi atualizada, e a partir de então um determinado SQL começou a causar problemas de performance, consumindo muitos recursos do servidor do Banco de Dados, e congelando tudo ou quase tudo que é executado naquele servidor? Pois é, eu já passei por isso várias vezes, e para resolver o problema há várias soluções. Até o 11G eu costumava matar as sessões do usuário que estava executando o(s) SQL(s) ruim(ns) e configurava o DRM (Database Resource Manager) para restringir àquele usuário o uso de CPU em até apenas 10% para que ele não prejudicasse os demais usuários e sistemas daquele BD. Após resolverem o problema do SQL eu retirava as limitações de CPU no DRM. 

     Matar as sessões é um procedimento radical e muitas vezes causa problemas no "pool de conexões", impedindo que o usuário conecte-se novamente, e forçando um restart nele. A partir do 12c, ao invés de começar matando as sessões do usuário dono do SQL ruim, existe a opção de cancelar os SQLs dele através do comando ALTER SYSTEM CANCEL SQL 'sid , serial'. Para facilitar esse trabalho, criei o script abaixo que permite cancelar os SQLs de todas as sessões de um determinado usuário (informado por meio da variável de substituição &USER):

SET SERVEROUTPUT ON

BEGIN
    -- elimina SQLS de sessões de um determinado usuário conectado no BD
    DBMS_OUTPUT.ENABLE(NULL);
    FOR CUR_TAB IN (SELECT  SID,
                            USERNAME,                           
                            'ALTER SYSTEM CANCEL SQL ''' || SID || ', ' || SERIAL# || '''' as cmd
                    FROM    V$SESSION
                    WHERE   USERNAME = UPPER('&USER')
                    AND     USERNAME IS NOT NULL) LOOP
        BEGIN
          EXECUTE IMMEDIATE CUR_TAB.CMD;
          dbms_output.put_line('O SQL da sessão ' || CUR_TAB.SID || ' do usuário ' ||  CUR_TAB.USERNAME || ' foi cancelado!');
        EXCEPTION
          WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Erro ao cancelar o SQL da sessão ' || CUR_TAB.SID || '. ' || SQLERRM);
        END;
    END LOOP;
END;

    O script é simples, mas bastante útil. Costumo usá-lo para liberar a carga do servidor rapidamente e dar um fôlego para analisar melhor o problema depois. Espero que ele também lhe ajude!

[]s