Missing Sequence Numbers in a Table...

Title says it all I guess.

But still want to blog out though...

Its quite common that at least one column in one table in the database will be depending on Oracle SEQUENCE and there will be a code in place to get the NEXTVAL from that sequence to populate.
And I am pretty sure that because of few reasons the NEXTVAL that is fetched is not really committed to the table and thus there will be a sequence miss in the table.

The following query gets you all the sequences that were generated but never made it table.

----------------------------------------------------------------------------------------------

--with one sql. only catch is that the number of rows in the actual table should be

--less than or equal to the number of rows in dba_objects.

select missing_seqs

from (

select rownum missing_seqs from dba_objects

minus

select seq from santest --replace with the actual column an table name here

)

where missing_seqs <= (select max(seq) from santest); --replace with the actual column an table name here

----------------------------------------------------------------------------------------------

--pl/sql block for without any catches and gotchas...

set serveroutput on size unlimited

declare

t_missing_seq number;

t_start_seq number := 1; --replace this with the actual seq value that is started off in the table

t_max_seq number;

x char;

begin

select max(seq) into t_max_seq from santest; --replace santest with the actual table where the sequence column is. also replace (seq) witht the actual column name

for i in t_start_seq..t_max_seq loop

begin

select 'x'

into x

from santest --replace santest with the actual table

where seq = i; --replace seq with the actual column name

exception

when no_data_found then

dbms_output.put_line('Missing Sequence --> '||i);

end;

end loop;

end;

/

----------------------------------------------------------------------------------------------