Matando sessões no Oracle Database – Métodos efetivos explicados

Matando sessões no Oracle Database

Introdução

No artigo de hoje vou comentar sobre como Matar (ou eliminar) sessões de usuários em Bancos de Dados Oracle, assunto que já vi muitas dúvidas em fóruns e grupos de discussão. Essas dúvidas normalmente surgem em profissionais que trabalham com Banco de Dados (BD) Oracle, e que não sabem ao certo qual o melhor método (ou método efetivo) para matar uma sessão de usuário do BD.

Em 1º lugar, é importante entender que existem várias formas de executar esta atividade e que cada uma tem as suas características, vantagens e desvantagens, e é isso que vou explicar neste artigo! Algumas das causas mais comuns que demandam a atividade de matar sessões de usuários no BD são:

a) Atualizações de sistemas:

Surge a necessidade de atualizar um sistema e para fazer essa atualização é necessário que nenhum usuário desse sistema esteja conectado no BD.

b) Sistemas lentos ou congelados:

Um determinado sistema, executando um SQL no BD, apresenta lentidão ou encontra-se em um estado praticamente congelado. Nestes casos, devemos matar as sessão(ões) que está(ão) bloqueando ou consumindo muitos recursos, para depois encontrar e corrigir a origem o problema.

c) Execução de operações indevidas:

Uma determinada funcionalidade de um sistema está executando uma instrução SQL no BD, mas foi iniciada erroneamente e o seu SQL precisa ser cancelado.

Quando nos deparamos com alguma dessas situações, para matar a sessão de usuário, precisamos primeiro identificar qual é a sessão, para depois matá-la através de 1 de 3 métodos disponíveis, que apresentarei ao longo deste artigo.

Identificando a sessão de usuário:

Para identificar a sessão de usuário que precisamos matar, execute 1 de 2 consultas que apresentarei abaixo:

1- Pesquisar nas visões V$SESSION e V$PROCESS, o valor das colunas SID, SERIAL# e SPID, filtrando por alguma informação que temos do usuário, tal como: nome da conta de usuário no BD ou do Sistema Operacional (SO), nome do programa ou módulo que ele está executando, nome da máquina que abriu conexão com o BD etc.

Exemplo (descomente as linhas AND da cláusula WHERE, se necessário, fazendo as devidas substituições):

         SELECT  S.SID, S.SERIAL#, P.SPID, S.USERNAME, 
               S.STATUS, S.OSUSER, S.MACHINE, 
               S.PROGRAM, S.MODULE, 
               TO_CHAR(S.LOGON_TIME, 'dd/mm/yyyy hh24:mi:ss') LOGON_TIME, 
               S.blocking_session -- id da sessao bloqueadora (qdo for o caso)
       FROM    V$SESSION S
       JOIN    V$PROCESS P
         ON    P.addr = S.paddr
       WHERE   S.TYPE = 'USER'          
       -- AND  S.USERNAME = 'X1' -- substituir X1 pelo nome do usuario no BD
       -- AND  S.OSUSER = 'X2'  -- substituir X2 pelo nome do usuario no SO
       -- AND  S.MACHINE = 'X3' -- substituir X3 pelo nome da máquina q está se conectando no BD
       -- AND  S.PROGRAM = 'X4' -- substituir X4 pelo nome do programa q está se conectando no BD

2- Pesquisar nas visões V$SESSION e V$SQL, o valor das colunas SID, SERIAL# e SPID,  utilizando os mesmos filtros do item anterior (se for necessário) + 1 filtro pela instrução SQL que foi executada.

Exemplo (descomente as linhas AND da cláusula WHERE, se necessário, fazendo as devidas substituições):

         SELECT  S.SID, S.SERIAL#, P.SPID, S.USERNAME,
               S.STATUS, S.OSUSER, S.MACHINE,
               S.PROGRAM, S.MODULE,
               TO_CHAR(S.LOGON_TIME, 'dd/mm/yyyy hh24:mi:ss') LOGON_TIME,
               S.blocking_session, -- id da sessao bloqueadora (qdo for o caso)
               DBMS_LOB.SUBSTR(a.SQL_FULLTEXT, 4000,1) sql_text
         FROM    V$SESSION S
       JOIN    V$PROCESS P
         ON    P.addr = S.paddr
         JOIN    V$SQLAREA A
          ON   s.sql_hash_value = a.hash_value
         WHERE   TYPE = 'USER'
         -- AND  S.USERNAME = 'X1' -- substituir X1 pelo nome do usuario no BD
         -- AND  S.OSUSER = 'X2'  -- substituir X2 pelo nome do usuario no SO
         -- AND  S.MACHINE = 'X3' -- subst. X3 pelo nome da máquina q está se conectando no BD
         -- AND  S.PROGRAM = 'X4' -- subst. X4 pelo nome do programa q está se conectando no BD
         -- AND  A.sql_text like '%X5%' -- subst. X5 por parte do SQL desejado

Matando a sessão de usuário:

Agora com o SID, SERIAL# e SPID da sessão desejada em mãos, podemos matá-la utilizando 1 de 3 métodos disponíveis:

1- Comando ALTER SYSTEM KILL SESSION

O comando ALTER SYSTEM KILL SESSION é a opção mais segura para matar uma sessão de usuário. Para executá-lo, precisamos apenas do SID e SERIAL# da sessão. A sintaxe básica dele é:

SQL> ALTER SYSTEM KILL SESSION 'sid, serial#'; -- substitua sid e serial# pelos valores encontrados no passo anterior

Se você for executar este comando em um ambiente RAC, opcionalmente acrescente o identificador da instância (ou INST_ID), consultando a visão GV$SESSION, ao invés de V$SESSION, como no exemplo abaixo:

SQL> ALTER SYSTEM KILL SESSION 'sid, serial#, @inst_id';

Um problema do comando apresentado acima é que ele não mata a sessão imediatamente. Ele apenas pede para ela se matar. Este comando foi projetado para finalizar a sessão de forma sutil, enviando uma mensagem para o usuário do tipo ORA-00028: your session was killed, e aguardando uma resposta dele. Se a sessão tiver uma transação ativa, um rollback deverá ocorrer e somente depois desta operação é que o usuário receberá a mensagem. Se a sessão estiver ociosa, o usuário só receberá a mensagem de erro quando tentar usar novamente o sistema vinculado àquela sessão. O mais importante é entender que após executar este comando, a sessão só será encerrada definitivamente quando o usuário tentar operar o sistema, após a mensagem de erro. Neste momento, o processo de usuário envia uma resposta para o processo servidor do SGBD e finalmente a sessão é encerrada! Em alguns casos, a sessão poderá ficar bloqueada (ou poderá falhar)e o processo de usuário não conseguirá notificar o processo servidor. Nestes casos, adivinhe o que irá acontecer? O processo de servidor ficará eternamente aguardando a resposta da sessão de usuário e a sessão nunca será finalizada (isso já aconteceu comigo várias vezes)! Por este motivo, existem os métodos que apresentarei nos itens 2 e 3.

O comando ALTER SYSTEM KILL SESSION possui a cláusula IMMEDIATE, que pode ser utilizada para retornar o controle da sessão imediatamente.  Sem esta cláusula, ao executá-lo, você terá que aguardar o rollback de alguma transação existente. Exemplo:

SQL> ALTER SYSTEM KILL SESSION 'sid, serial#' IMMEDIATE;

2- Comando ALTER SYSTEM DISCONNECT SESSION

O comando ALTER SYSTEM DISCONNECT SESSION é um método mais efetivo para matar sessões de BD e deve ser usado quando for desejado matar e desconectar uma sessão de modo forçado, imediatamente. Este método libera os recursos utilizados pela sessão de forma realmente imediata, mas pode ser perigoso! Ao invés de solicitar que a sessão faça a sua auto-eliminação, ele mata diretamente o processo servidor do SO vinculado à ela. Por este motivo, tenha cuidado ao executá-lo em ambientes com Conexões Compartilhadas, pois ao tentar matar uma sessão, você poderá matar várias sessões que estão vinculadas ao mesmo processo servidor do SO.

A sintaxe básica deste comando é similar ao do comando ALTER SYSTEM KILL SESSION com a cláusula adicional POSTTRANSACTION (aguarda transações em execução terminarem, sem forçar rollback) ou IMMEDIATE (finaliza e faz rollback nas transações em execução, imediatamente).

Para executar o comando ALTER SYSTEM KILL SESSION, precisamos apenas do SID e SERIAL# da sessão, como nos exemplos abaixo:

SQL> ALTER SYSTEM DISCONNECT SESSION 'sid, serial#' POST_TRANSACTION

SQL> ALTER SYSTEM DISCONNECT SESSION 'sid, serial#' IMMEDIATE;

A grande vantagem de executar este comando é que ele permite liberar os recursos da sessão imediatamente, pois ele desconecta a sessão e deixa para o PMON (Process Monitor) finalizar a transação!

3- Comando KILL

O comando KILL é um método similar ao anterior, mas com uma pequena diferença: ao invés de matar a sessão através de um comando do SGBD, ele irá matá-la através de um comando do SO. Para executá-lo você precisará apenas do SPID da sessão.

Exemplo de como executar o KILL em SO Windows (fornecendo também o ORACLE_SID do BD desejado):

C:> orakill ORACLE_SID spid

Exemplos de como executar o KILL em SO Unix/Linux:

kill spid   -- pede p/ o processo se auto-eliminar de forma segura

ou  :

kill -9spid    -- pede p/ o processo se auto-eliminar de forma bruta, imediatamente

Espero que o artigo seja útil!

Abraços