Can I implement a 'gapless' identity column in Oracle?
The only way to guarantee a gapless series (assuming no deletes) is to serialize - almost always a bad idea

With a sequence or identity you cannot assume your series will be gapless as @a_horse mentions - but it looks like you are assuming that even with gaps, there is some relationship between sequence order and insertion order - this is not true either! A higher sequence number could be inserted before a lower. Therefore trying to join "consecutive" rows is a meaningless concept

example:

create sequence seq;
create table t1(id integer);
create table t2(id integer);

--session 1:
insert into t1 select seq.nextval from dual connect by level&lt;1000000;
commit;

--session 2:
insert into t2 select seq.nextval from dual connect by level&lt;1000000;
commit;


result:

select min(id), max(id) from t1 union all select min(id), max(id) from t2;

MIN(ID)                MAX(ID)                
---------------------- ---------------------- 
1                      1671679                
356547                 1999998