Ressetando Sequências no Oracle

Pessoal,

     Uma problema muito comum que os desenvolvedores que trabalham com Bancos de Dados Oracle enfrentam, ao testar suas aplicações em ambientes de Desenvolvimento e Homologação, é a impossibilidade de ressetar ou redefinir valores de sequências (sequences) após terminar testes que necessitam limpar ou voltar o estado anterior dos dados afetados.
    
     No Oracle, as sequências não podem ser ressetadas ou redefinidas para um determinado valor. Para ressetar (determinar ou reatribuir o valor inicial), por exemplo, uma sequência que tem o valor de início 1, que termina em 1000 e que tem o valor atual de 353, o único meio é configurar reciclagem de valores e chamar o método NEXTVAL da sequence inúmeras vezes até que ela chegue ao número final (1000) + 1.

     Como este processo é bem trabalhoso, muitos DBA´s apenas apagam e recriam as sequências, o que é não é um método muito seguro, pois neste processo, ao apagar a sequência, todos os usuários que possuíam privilégios de acesso sobre ela, perdem estes privilégios. Para evitar este problema e facilitar o trabalho dos desenvolvedores e/ou DBA´s, podemos criar a procedure abaixo, dar privilégio de EXECUTE nela para os desenvolvedores que precisam ressetar suas sequências e depois instruí-los a chamar esta procedure passando apenas o nome do owner da sequência e o nome da própria sequência.
        Ex.: BEGIN
                SP_SEQUENCE_RESET(SCHEMA_NAME, SEQUENCE_NAME);
             END;
               
--- ínicio do código da procedure
create or replace PROCEDURE SP_SEQUENCE_RESET(schema_name VARCHAR2, sequence_name VARCHAR2)
AUTHID current_user is
BEGIN
--------------------------------------------------------------------------------
--  SP_SEQUENCE_RESET
--------------------------------------------------------------------------------
-- PARÂMETROS
--------------------------------------------------------------------------------
-- entrada: schema_name ->  nome do schema proprietário da sequence a ser resetada
-- entrada: sequence_name -> nome da sequence a ser resetada
--------------------------------------------------------------------------------
-- Criação: 19/10/2010
-- Autor: Fábio Prado
-- Descrição: Procedure para ressetar sequences
-- Detalhes: Utiliza os privilégios do usuário que está chamando a procedure (AUTHID current_user).
-- O usuário que chama a procedure deve ter privilégios para selecionar e alterar a sequence informada.
--------------------------------------------------------------------------------
    DECLARE plast_number number;
            PCYCLE_FLAG CHAR(1);
            pmin_value NUMBER;
            pmax_value NUMBER;
            psequence_name VARCHAR2(30);
            powner_name VARCHAR2(30);
            pstrSQL VARCHAR2(1000);
            pseq_number number;
    BEGIN
        BEGIN       
            powner_name:=UPPER(schema_name);
            psequence_name:=UPPER(sequence_name);          
            BEGIN
            --1) Testa sequência para ver se ela já foi inicializada, para evitar erro ORA-08002
            PSTRSQL:='SELECT ' || POWNER_NAME || '.' || PSEQUENCE_NAME || '.CURRVAL FROM DUAL';
            --dbms_output.put_line(pstrSQL);
            EXECUTE IMMEDIATE pstrSQL INTO pseq_number;
            EXCEPTION
              WHEN OTHERS THEN
                -- Entra nesse erro quando sequence já estiver no valor limite
                IF INSTR(UPPER(SQLERRM(SQLCODE)),'ORA-08004') <> 0 THEN
                  -- nao faz nada, só trata erro para continuar processamento
                  NULL;
                END IF;            
            END;
            --2 ) Verifica qual o último valor registrado na seqüência com a seguinte instrução.
            SELECT  MIN_VALUE, MAX_VALUE, CYCLE_FLAG, LAST_NUMBER
            into    pmin_value, pmax_value, pcycle_flag, plast_number
            from    all_sequences d
            where   d.sequence_owner = UPPER(powner_name)
            AND     d.sequence_name = UPPER(psequence_name);
            dbms_output.put_line('Valor atual da sequência: ' || (plast_number));
 
            --3) se número atual da sequence é maior que maxvalue entao iguala valores
            IF plast_number > pmax_value THEN
              pmax_value:= plast_number;
            END IF;
            --4) Altera o minvalue para zero e maxvalue para o mesmo valor encontrado no last_value no resultado da instrução acima.
            pstrSQL:='alter sequence ' || powner_name || '.' || psequence_name || ' minvalue 0 maxvalue ' || plast_number || ' cycle';
            dbms_output.put_line(pstrSQL);
            EXECUTE IMMEDIATE pstrSQL;
            --5) Executa o Nextval múltiplas vezes até valor da sequencia for igual valor inicial
            WHILE (pseq_number <> pmin_value) LOOP
                PSTRSQL:='SELECT ' || POWNER_NAME || '.' || PSEQUENCE_NAME || '.NEXTVAL FROM DUAL';
                --dbms_output.put_line(pstrSQL);
                EXECUTE IMMEDIATE pstrSQL INTO pseq_number;       
            END LOOP;           
            --6) Reatribui valores originais de minvalue, maxvalue e cache da sequencia
            IF PCYCLE_FLAG = 'N' THEN
                pstrSQL:='alter sequence  ' || powner_name || '.' || psequence_name || ' minvalue ' || pmin_value || ' maxvalue ' || pmax_value || '  nocycle';
            ELSE
                pstrSQL:='alter sequence  ' || powner_name || '.' || psequence_name || ' minvalue ' || pmin_value || ' maxvalue ' || pmax_value;
            END IF;
            dbms_output.put_line(pstrSQL);
            EXECUTE IMMEDIATE pstrSQL;   
            DBMS_OUTPUT.PUT_LINE('Procedimento executado com sucesso');
            COMMIT;
        END;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            dbms_output.put_line('Procedimento cancelado: O nome da sequência ' || powner_name || '.' || psequence_name || ' não existe.');
        WHEN OTHERS THEN
            IF INSTR(UPPER(SQLERRM(SQLCODE)),'ORA-08002') <> 0 OR
                INSTR(UPPER(SQLERRM(SQLCODE)),'ORA-04004') <> 0 THEN
                dbms_output.put_line('Procedimento cancelado: Não foi possível ressetar a sequência ' || powner_name || '.' || psequence_name || ' não inicializada.');
            ELSE
                dbms_output.put_line('Erro ao ressetar a sequência ' || powner_name || '.' || psequence_name || ': ' || SQLERRM);
            END IF;
    END;
END;

    Obs.: A procedure, após executada, não altera as configurações (cache, order, cycle etc.) da SEQUENCE. Para ressetar uma sequência é necessário que o usuário que está chamando a procedure tenha privilégios de SELECT e ALTER na sequência informada.