Alterar valor de uma sequence

Acho que este cenário é bem comum: a chave primária de uma tabela tem seus valores definidos a partir de uma sequence. Por exemplo, os valores na tabela Customer, coluna Customer_ID são obtidos da sequence Customer_Seq. Se tudo funcionar adequadamente, o valor máximo na coluna é igual ao valor atual da sequence. E na próxima inserção de uma linha, a sequence retorna um valor maior (provavelmente um número acima). 

Pois bem, por diferentes motivos isto pode não ocorrer. Um caso comum ocorre no ambiente de desenvolvimento quando a aplicação ainda em testes iniciais contem um erro, insere muitas linhas sem utilizar a sequence e perde esta sincronia. Em produção já enfrentei um cenário mais complexo envolvendo duas aplicações inserindo dados concorrentemente em um BD. De qualquer modo, aparece uma necessidade: "adiantar a sequence até o ponto máximo da tabela".  

O modo simples seria dropar e recriar a sequence. Mas há problemas de segurança, concorrência, necessidade de recompilar pacotes, etc... que podem tornar a operação não muito simples. 

A solução óbvia é escrever um pequeno loop para adiantar a sequence n vezes. Atenção, se n for grande, isto pode demorar algum tempo.

Bem, depois de ocorrer algumas vezes, desenvolvi uma procedure para me ajudar. São três parâmetros: nome da tabela, nome da coluna e nome da sequence.  A idéia básica é obter a diferença entre o valor máximo na tabela, que suponho ser maior, e o valor atual na sequence. Então, alterar o incremento da sequence para a diferença e incrementar a sequence uma única vez cobrindo toda diferença. Apenas para não afetar a sequence de maneira definitiva, é necessário saber o incremento utilizado no início e redefini-lo no final. 

Vantagens: 

• em poucos passos o valor da sequence é corrigido 
• tempo de execução não depende da diferença
• a sequence continua existindo, bem como seus grants
• não é necessário recompilar procedures ou funções
• e uma vez escrita a procedure, basta chamá-la.

Para chamar a procedure, basta uma linha como;

exec move_sequence('customer', 'customer_id', 'customer_seq');

Aqui a procedure, com alguns put_line apenas para ajudar a sua compreensão:

create or replace
procedure move_sequence(p_table_name varchar2, p_column_name varchar2, p_sequence_name varchar2 )
as
  sqlc varchar2(500);
  max_column_value number;
  curr_seq_value number;
  diff number;
  curr_seq_increment number;


begin


  sqlc := 'select max('||p_column_name||') from '||p_table_name;
  execute immediate sqlc into max_column_value;
  
  dbms_output.put_line('Max Current Value: '||max_column_value);


  select us.increment_by
    into curr_seq_increment
    from user_sequences us
   where sequence_name = upper(p_sequence_name); 
  
  sqlc := 'select '||p_sequence_name||'.nextval from dual';
  execute immediate sqlc into curr_seq_value;
  
  dbms_output.put_line('Seq Current Value: '||curr_seq_value);

  diff := max_column_value - curr_seq_value;
  sqlc := 'alter sequence '||p_sequence_name||' increment by '||diff;
  execute immediate sqlc;
  
  sqlc := 'select '||p_sequence_name||'.nextval from dual';
  execute immediate sqlc into curr_seq_value;
  
  dbms_output.put_line('New Seq Current Value: ' ||curr_seq_value);
   
 sqlc := 'alter sequence '||p_sequence_name||' increment by '||curr_seq_increment;
  execute immediate sqlc;
  
end;

Uma variação simples é resetar a sequence para o valor inicial. Esta eu deixo por conta do leitor. Alguns detalhes podem ser incluídos, como um parâmetro para o nome do esquema.