Find gaps in Oracle sequences Oracle Tips by Burleson Consulting October 26, 2007

 

Oracle sequences allow the DBA to insert unique sequence numbers for artificial keys and maintain an auto-numbering system.

 

While Oracle has tools to detect sequencing gaps in archived redo logs (the v$archive_gap view), we have to write own own SQL to detect gaps in user-defined Oracle sequences.

 

This blog has an interesting use of the Oracle LEAD and LAG functions for SQL to detect gaps in Oracle sequence numbers, very creative:

 

SQL> WITH aquery AS
2 (SELECT measurement_idafter_gap,
3 LAG(measurement_id,1,0) OVER (ORDER BY measurement_id) before_gap
4 FROM sensor_data)
5 SELECT
6 before_gap, after_gap
7 FROM
8 aquery
9 WHERE
10 before_gap != 0
11 AND
12 after_gap - before_gap > 1
13 ORDER BY
14 before_gap;



BEFORE_GAP AFTER_GAP

---------- ----------
2 4
5 7