Oracle sequence is giving 21 instead of 3. I don't know why 21 but not other values. Please help to understand this.
Update I used this sequence for static reference table so that id can be used for java code to pull data which should be in order from 1 ,1+1,1+2 and so on.
create table table1 (id number(20) generated always as identity, name varchar2(20));
insert into table1(name) values('111');
insert into table1(name) values('222');
select * from table1;
--Output
1 111
2 222
insert into table1(name) values('333');
select * from table1;
21 333 -- problamatic id as 21 generated
1 111
2 222
Identity column is in the background based on a sequence value. Sequences guarantee uniqueness, but values they return aren't / don't have to be gapless.
Why do you feel that 21 is "problematic" value? What's wrong with it? It is as good as any other value.
If you want to return gapless values during select, then include e.g. row_number analytic function:
select row_number() over (order by id) rn, --> this
name
from table1;
and present it to users. Leave ID for your own purposes, when you have to access rows by it. End users don't care about ID anyway.
You commented that you
wanted to get values in sequence but which is not happening
"I want" doesn't help much. Why do you want it? What benefit do you expect?
There is a way to create gapless sequence of numbers, but (as far as I can tell) it requires some programming.
Also, there's an issue when you delete a row, somewhere in the middle of that sequence. What then? You'll again get a gap. Will you resequence all numbers? You could (theoretically), but - what if there are foreign keys that reference that ID? Modifying primary key values is rarely a good idea.
Shortly: don't do any of that. Accept that sequence will be unique with possible gaps and move on.