Find Unused Key Ranges

Finding unused key ranges or determining the lowest unused key is a common issue. Here is one of several possibilities to solve this problem.

Create the test table and populate it with test data:

-- create test table
create table nr_tab(nr number(10));

-- insert test values
insert into nr_tab values(1);
insert into nr_tab values(2);
insert into nr_tab values(4);
insert into nr_tab values(5);
insert into nr_tab values(8);

Get all key ranges which are not used:

select previous_nr+1  range_start
,      next_nr-1      range_end
from   ( 
 select lag(nr, 1, 0) over( order by nr) previous_nr
 ,      nr next_nr
 from   nr_tab
       )
where  previous_nr+1 < next_nr
/

RANGE_START  RANGE_END
----------- ----------
          3          3
          6          7

The inner select gets the nr and, using the analyical function lag, the value of the previous row (ordered by nr). The first argument to lag is the column to be retrieved, the second the number of records to look back (here 1), and the third is the default value if there is no preceding row found (0 here).
As we do want the ranges with missing numbers, we only select the rows whose previous nr (incremented by 1), is still smaller than the current nr (see where clause of outer select).

To see the range above the highest key used so far, we need to select it separately and add it to our result using UNION ALL (using union all is much quicker that just union as Oracle does not try to elminitate equal rows).
An index on the nr column makes sure this second select does not access the whole table again - it will just retrieve a few index blocks.
Specify the maximum number you want to allow using the variable max_nr_allowed.

select previous_nr+1  range_start
,      next_nr-1      range_end
from   ( 
 select lag(nr, 1, 0) over( order by nr) previous_nr
 ,      nr next_nr
 from   nr_tab
 union all
 select nvl(max(nr),0)
 ,      &max_nr_allowed+1
 from   nr_tab
       )
where  previous_nr+1 < next_nr
/

Enter a value for max_nr_allowed: 999

RANGE_START  RANGE_END
----------- ----------
          3          3
          6          7
          9        999

If you need to get the lowest available key, just select the minimum range_start.

select min(previous_nr+1)
from   ( 
 select lag(nr, 1, 0) over( order by nr) previous_nr
 ,      nr next_nr
 from   nr_tab
 union all
 select nvl(max(nr),0)
 ,      &max_nr_allowed+1
 from   nr_tab
       )
where  previous_nr+1 < next_nr
/

As the lower select of the inline view uses the max funtion, you will alwas get a result, even if there is no key used yet (the table is empty).

Another possible solution is to join the table to itself, you'll have to go this way if your Oracle database version does not support analytical functions yet.