Como limpar grandes bases ORACLE sem utilização de TRUNC

Código para limpar grandes tabelas comitando por blocos

Se precisarmos remover os registros de uma tabela e não podermos dar um TRUNC, pois existem relacionamentos e não temos permissão para desabilitar as constraints, o jeito é dar um DELETE mesmo. O problema é que para tabelas muito grandes, o espaço pode estourar por conta do tamanho da transação, além de deixar a operação muito demorada.

Uma forma interessante para resolver esse problema é ir comitando por partes, a cada “x” registros. Segue abaixo um script para realizar essa operação:

SET serveroutput ON format WRAPPED;
DECLARE
  linhas NUMBER(38) := 0;
  intervaloCommit NUMBER(6) := 20000;
  TYPE tipoVetor IS VARRAY(100) OF VARCHAR2(100);
  tabelas tipoVetor;
  
  TYPE tipoCursor IS REF CURSOR;
  resultadoConsulta tipoCursor;
  rId varchar2(100);
BEGIN
  tabelas := tipoVetor('tabela1', 'tabela2', 'tabela3');
  FOR nomeTabela IN 1 .. TABELAS.COUNT LOOP
      linhas := 0;
      OPEN resultadoConsulta FOR 'SELECT rowid FROM ' || tabelas(nomeTabela);
      LOOP
        FETCH resultadoConsulta INTO rId;
        EXIT WHEN resultadoConsulta%NOTFOUND;
        EXECUTE IMMEDIATE 'DELETE FROM ' || tabelas(nomeTabela) || ' WHERE rowid = ''' || rId || '''';
        linhas := linhas + 1;
        IF MOD(linhas, intervaloCommit) = 0 THEN
          COMMIT;
        END IF;
      END LOOP;
      COMMIT;
      DBMS_OUTPUT.put_line('Total de ' || linhas || ' linhas removidas de ''' || tabelas(nomeTabela) || '''');
  END LOOP;
END;

Com esse código, os commits serão realizados a cada 20 mil linhas excluídas.

O único problema é que não temos como ir exibindo os commits realizados a cada 20 mil linhas no console, pois o DBMS_OUTPUT.put_line fica gravando os valores em um buffer e só exibe o resultado após a conclusão da execução do bloco, não deixando possível o acompanhamento durante a execução.

Boa sorte.