Apagando dados de todas as tabelas de um schema

     Olá pessoal,

     No post de hoje vou compartilhar um script que pode ser muito útil no seu dia a dia. Ele permite que você apague dados de todas as tabelas de um determinado schema no Oracle Database, algo comum que nós DBAs fazemos em ambientes de homologação e desenvolvimento.

 

      O que o script abaixo faz para apagar dados de todas as tabelas de um schema no Oracle? Ele começa desabilitando as FKs (foreign keys) do schema desejado, em seguida executa um TRUNCATE nas tabelas do schema, e por fim, reabilita as FKs previamente desabilitadas:

-- Limpa todas as tabelas de um schema desabilitando as FKs e reabilitando-as ao final.
SET SERVEROUTPUT ON
DECLARE
   v_nome_schema VARCHAR2(30) := UPPER('&NOME_SCHEMA');
   v_command VARCHAR2(100);
   v_TIME DATE := SYSDATE;
BEGIN
   -- 1. Desabilita Fk´s
   FOR LINHA_CUR1 IN ( SELECT 'ALTER TABLE ' || A.OWNER || '."' || A.TABLE_NAME || '" DISABLE CONSTRAINT ' || A.CONSTRAINT_NAME AS CMD,
                              A.CONSTRAINT_NAME
                       FROM   DBA_CONSTRAINTS A
                       JOIN   DBA_CONSTRAINTS C
                         ON   A.R_CONSTRAINT_NAME = C.CONSTRAINT_NAME
                       WHERE  A.OWNER = v_nome_schema
                       AND    A.CONSTRAINT_TYPE = 'R'
                       AND    C.CONSTRAINT_TYPE = 'P'
                       AND    A.STATUS = 'ENABLED'

                      )
   LOOP
      BEGIN
          v_command := LINHA_CUR1.CMD;
          EXECUTE IMMEDIATE v_command;
          DBMS_OUTPUT.PUT_LINE('FK desabilitada: ' || LINHA_CUR1.constraint_name);
      EXCEPTION
         WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Falha ao executar commando: ' || v_command);
            DBMS_OUTPUT.PUT_LINE(sqlerrm);
      END;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('Passo 1 (desabilita fk´s) executado com sucesso');

   -- 2. Limpa tabelas
   FOR LINHA_CUR2 IN ( SELECT 'TRUNCATE TABLE ' || OWNER || '."' || TABLE_NAME || '"' AS CMD,
                               TABLE_NAME
                       FROM    DBA_TABLES
                       WHERE   OWNER=v_nome_schema

                      )
   LOOP
      BEGIN
         v_command := LINHA_CUR2.CMD;
         EXECUTE IMMEDIATE v_command;
         DBMS_OUTPUT.PUT_LINE('Truncate executado na tabela: ' || LINHA_CUR2.TABLE_NAME);
       EXCEPTION
          WHEN OTHERS THEN
             DBMS_OUTPUT.PUT_LINE('Falha ao executar commando: ' || v_command);
             DBMS_OUTPUT.PUT_LINE(sqlerrm);
       END;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('Passo 2 (limpa tabelas ) executado com sucesso');

   -- 3. Reabilita Fk´s
   FOR LINHA_CUR3 IN ( SELECT 'ALTER TABLE ' || A.OWNER || '."' || A.TABLE_NAME || '" ENABLE CONSTRAINT ' || A.CONSTRAINT_NAME AS CMD,
                               A.CONSTRAINT_NAME
                       FROM    DBA_CONSTRAINTS A
                       JOIN    DBA_CONSTRAINTS C
                         ON    A.R_CONSTRAINT_NAME = C.CONSTRAINT_NAME
                       WHERE   A.OWNER = v_nome_schema
                       AND     A.CONSTRAINT_TYPE = 'R'
                       AND     C.CONSTRAINT_TYPE = 'P'
                       AND     A.STATUS = 'DISABLED'
                       AND     A.LAST_CHANGE >= V_TIME)
   LOOP
      BEGIN
         v_command := LINHA_CUR3.CMD;
         EXECUTE IMMEDIATE v_command;
         DBMS_OUTPUT.PUT_LINE('FK habilitada: ' || LINHA_CUR3.constraint_name);
      EXCEPTION
         WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Falha ao executar commando: ' || v_command);
            DBMS_OUTPUT.PUT_LINE(sqlerrm);
      END;
   END LOOP;

   DBMS_OUTPUT.PUT_LINE('Passo 3 (reabilita Fk´s) executado com sucesso');
END;

ATENÇÃO: Antes de executar o script acima certifique-se de que não está conectado no ambiente de produção! Uma execução incorreta pode ter consequências graves: cada linha apagada erroneamente em uma tabela pode acarretar no "fim trágico" de um bebê foca na Antártida!