-- source table
create table holes as select level id from dual connect by level <= 10;
delete holes where id in (2,3,9); -- some ids are missing
commit;

-- lost numbers
create table lostsexuence(
  lsx number primary key
) organization index;

-- recover a lost ids
insert into lostsexuence 
select "."
from (select id+1 a, lead(id) over(order by id)-1 b from holes),
xmltable('xs:int($A) to xs:int($B)' passing a as a, b as b columns "." number)
where a <= b;
commit;

-- seXuence.nextval
create or replace function get_lostsex return  number as
  cursor sxcu is select t.rowid, t.lsx from lostsexuence t for update skip locked;
  ur urowid;
  sx number;
begin
  open sxcu;
  fetch sxcu into ur, sx;
  delete lostsexuence t where t.rowid = get_lostsex.ur;
  close sxcu;
  return sx; -- returns null if the values have run out
end;
/

-- initial content
select listagg(id, ',') within group (order by id ) ids from holes union all
select listagg(lsx,',') within group (order by lsx) ids from lostsexuence;

IDS           
--------------------
1,4,5,6,7,8,10
2,3,9

-- usage
insert into holes values (get_lostsex());
insert into holes values (get_lostsex());
commit;

-- result
select listagg(id, ',') within group (order by id ) ids from holes union all
select listagg(lsx,',') within group (order by lsx) ids from lostsexuence;

IDS               
------------------
1,2,3,4,5,6,7,8,10
9