DROP USER: Removendo um schema no Oracle com a stored procedure DROP_USER_DISCONNECT_SESSION

Olá,

Imagine vários ambientes de bancos de dados como desenvolvimento, teste, homologação, produção, entre outros. Particularmente, em um ambiente de desenvolvimento, é comum na rotina de um DBA realizar tarefas de exportação e importação de schemas de bancos de dados. Dependendo da frequência desta tarefa, por exemplo, no caso de realizar uma importação para substituir um schema existente em um banco de dados, o schema atual precisará ser dropado. O problema acontece quando ao realizarmos o DROP de um schema, o erro "ORA-01940: não é possível eliminar um usuário conectado no momento" surge na tela. O que fazemos então? Acessamos a view dinâmica de desempenho V$SESSION para descobrir o SID e SERIAL# das sessões conectadas para então desconectá-las.

Bom, neste artigo irei compartilhar uma stored procedure chamada de DROP_USER_DISCONNECT_SESSION criada por mim para facilitar um pouco esta tarefa. O objetivo dela será basicamente dropar um schema no banco de dados. A vantagem de usá-la é que a mesma fará todo o trabalho de desconectar as sessões correntes, bloquear o schema (account lock) de forma a impedir que novas conexões sejam realizadas enquanto a stored procedure estiver desconectando as sessões atuais, e por fim, dropar o schema. Segue abaixo uma simulação para demonstrar o seu uso.

C:\>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Seg Set 1 11:58:37 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning option

SQL> drop user scott cascade;
drop user scott cascade
*
ERRO na linha 1:
ORA-01940: não é possível eliminar um usuário conectado no momento

Acima, é possível verificar  que, ao tentar o dropar o schema SCOTT, a mensagem de erro ORA-01940 foi emitida. No resultado do SQL abaixo poderemos verificar consultando a view de desempenho dinâmico V$SESSION que 3 sessões estão conectadas atualmente.
 

SQL> select sid,serial#,username,status,terminal
  2    from v$session
  3   where username='SCOTT';

       SID    SERIAL# USERNAME               STATUS      TERMINAL
---------- ---------- ---------------------- ----------- ----------------
        26         16 SCOTT                  INACTIVE    SERVER01
        28         17 SCOTT                  INACTIVE    MACHINE09
        34         41 SCOTT                  ACTIVE      MACHINE03

Irei criar abaixo a stored procedure DROP_USER_DISCONNECT_SESSION e executá-la. Para evitar quaisquer problemas com privilégios, irei criá-la no usuário SYS. Se desejar, a criação de um sinônimo e a concessão do privilégio EXECUTE poderá ser concedido a um outro usuário no banco de dados, como por exemplo, o SYSTEM.
 

SQL> create or replace procedure drop_user_disconnect_session (owner varchar)
  2  as
  3  begin
  4     declare
  5        l_cnt   integer;
  6     begin
  7        begin
  8           execute immediate 'alter user ' || upper (owner) || ' account lock';
  9        exception
 10           when others
 11           then
 12              null;
 13        end;
 14
 15        for c1 in (select *
 16                    from v$session
 17                   where username = upper (owner))
 18        loop
 19           begin
 20              execute immediate
 21                    'alter system disconnect session '''
 22                 || c1.sid
 23                 || ','
 24                 || c1.serial#
 25                 || ''' immediate';
 26           exception
 27              when others
 28              then
 29                 null;
 30           end;
 31        end loop;
 32
 33        loop
 34           select count (*)
 35             into l_cnt
 36             from v$session
 37            where username = upper (owner);
 38
 39           exit when l_cnt = 0;
 40           dbms_lock.sleep (10);
 41        end loop;
 42
 43        begin
 44           execute immediate 'drop user ' || owner || ' cascade';
 45        exception
 46           when others
 47           then
 48              null;
 49        end;
 50     end;
 51  exception
 52     when others
 53     then
 54        null;
 55  end;
 56  /

Procedimento criado.

Pronto. Após a criação da mesma, segue abaixo sua execução passando como parâmetro o schema SCOTT.
 

SQL> exec drop_user_disconnect_session('SCOTT');

Procedimento PL/SQL concluído com sucesso.

Após a execução da mesma, poderemos observar abaixo que as sessões foram desconectadas e o schema SCOTT foi dropado com sucesso.
 

SQL> select sid,serial#,username,status,terminal
  2    from v$session
  3   where username='SCOTT';

não há linhas selecionadas

SQL> select * from all_users where username='SCOTT';

não há linhas selecionadas