Identificando lacuna de valores

Cenários atípicos fazem parte constantemente da vida de um DBA, mas recentemente me deparei com algo que... Caramba, a bagunça está grande!

Me informaram que começou a gerar o erro ORA-00001 unique constraint violation na aplicação e que esta coluna, chamada aqui de ID era alimentada utilizando os valores retornados por uma sequence, mas que de um dia para o outro o last_number havia regredido, logo, o nextval retornava um valor já existente na coluna e gerava o erro.

A primeira coisa que fui verificar era se a sequence era CYCLE, ou seja, ao chegar ao seu limite ela voltaria ao valor inicial. Sim, era CYCLE, mas já estava em 160.000.000 de 999999999 (max_value). Sendo ela incrementada de 1 em 1 e sem cache (nocache), era pouco provável que já havia sido requisitado 160 milhões de vezes em poucas horas.

Neste momento já percebi que alguem tinha dado alguma dedada errada, mas como o problema estava gerando sérios impactos, parti logo para a solução.

Simples, pegamos o maior valor da coluna ID e ajustamos a sequence. Ai a coisa começou a ficar mais interessante pois me informaram que não poderia ser assim porque os valores 999999997, 999999998, 999999999 já haviam sido usados.. OK, então. E para qual valor devo ajustar? Veja, bem! Temos que descobrir um buraco.... ham??

Como deveria ser:

A coluna ID da tabela sendo alimentada com base no retorno da sequence X, a cada N dias uma rotina apaga os valores mais antigos e a sequence ao chegar aos 999999999 reinicia, desta forma os valores (registros) antigos já foram apagados e o valor pode ser novamente inserido na coluna ID sem erros formando assim um ciclo saudável.

Como é:

Já foi inserido valores (ID) de varios ranges diferentes, sequence ajustada para começar a partir de vários valores.. quando gerava erro era sorteado um novo "buraco" de valores. Exemplo: valores inseridos de 1 a 10, depois de 500 a 900, 20000 a 50000, ou seja, haviam varias lacunas sem sentido.

Não tive resposta de como a bagunça foi gerada, mas era coisa antiga e precisava ser ajustado a sequence para que as coisas voltassem a funcionar.

Fiz o solicitado e deixei a recomendação para arrumarem a casa o mais breve possível.

No exemplo abaixo compartilho a query que utilizei para identificar as lacunas de valores.

SQL> create table tst (id number);

Table created.

SQL> insert into tst values (1);

1 row created.

SQL> insert into tst values (2);

1 row created.

SQL> insert into tst values (10);

1 row created.

SQL> insert into tst values (12);

1 row created.

SQL> insert into tst values (60);

1 row created.

SQL> insert into tst values (70);

1 row created.

SQL> insert into tst values (500);

1 row created.

select prev_id || '...' || id || ' = ' || diff as lacuna
  from (select id,
               lag(id) over(order by id) prev_id,
               id - lag(id + 1) over(order by id) diff
          from (select id from sys.tst 
                union 
                select 999999999 id from dual) --max_value
                )
 where diff > 0
/

LACUNA
------------------------------
2...10 = 7
10...12 = 1
12...60 = 47
60...70 = 9
70...500 = 429
500...999999999 = 999999498

Na query foi utilizado a função LAG existente desde a versão 8i e que permite que seja acessado o valor anterior (previous row) na linha atual.

Referência:
https://docs.oracle.com/database/121/SQLRF/functions094.htm#SQLRF00652