Use the lag and lead functions to find missing sequence numbers

Tzxd @ webora9> spool AAA. Log
Tzxd @ webora9> drop table test;
Drop Table Test
*
Error at line 1:
ORA-00942: Table or view does not exist

Tzxd @ webora9> Create Table Test (ID, object_name)
2 select rownum, object_name from OBJ where rownum <= 20;

Table created.

Tzxd @ webora9> Delete from test where ID in (5, 9, 15 );
Here, we will first delete three discontinuous data records to see how the result works.
3 rows deleted.

Tzxd @ webora9> Col object_name format A32
Tzxd @ webora9> select ID, object_name from test;

Id object_name
------------------------------------------
1 acc_fee_code
2 acc_user_bill_dtl_576_200401
3 acc_user_bill_dtl_576_200402
4 acc_user_bill_dtl_576_200403
6 acc_user_bill_dtl_576_200405
7 acc_user_bill_dtl_576_200406
8 all_cell_info
10 all_cell_info_temp
11 all_cell_info_temp_1
12 all_cell_info_temp_2
13 all_cell_info_temp_3
14 analize_xd
16 analize_xd_post
17 analize_xd_pre
18 area_200406
19 area_200406_count
20 area_200406_temp1

17 rows selected.

Tzxd @ webora9> select ID, ID-lag_val diff_1, lead_val-ID diff2, lag_val, lead_val,
Object_name 2
3 from (
4 select ID, lag (ID, 1, 0) over (order by ID) lag_val,
5 lead (ID, 1, 0) over (order by ID) lead_val, object_name
6 from test
7)
8 where lead_val-id <> ID-lag_val
9/

Id diff_1 diff2 lag_val lead_val object_name
----------------------------------------------------------------------------------
4 1 2 3 6 acc_user_bill_dtl_576_200403
/*5Diff_2-diff_1 = 2-1 = 1 data missing after 4 */
6 2 1 4 7 acc_user_bill_dtl_576_200405
8 1 2 7 10 all_cell_info
/*9After 8, diff_2-diff_1 = 2-1 = 1 data is missing 9 */
10 2 1 8 11 all_cell_info_temp
14 1 2 13 16 analize_xd
/*15After 14, diff_2-diff_1 = 2-1 = 1 data is missing 15 */
16 2 1 14 17 analize_xd_post
20 1-20 19 0 area_200406_temp1

7 rows selected.

Tzxd @ webora9> spool off