oracle autoincrement with sequence and trigger is

问题:

here is my problemI have this code to make an autoincrement variable in oracle database:

CREATE TABLE Korisnici
    (
        id_korisnika number PRIMARY KEY,
        ime_korisnika varchar2(200),
        prezime_korisnika varchar2(200),
        broj_telefona varchar2(30),
        adresa_korisnika varchar2(400)
    )
    /

create sequence test_seq
start with 1 
increment by 1;

CREATE OR REPLACE TRIGGER test_trigger
BEFORE INSERT ON Korisnici FOR EACH ROW
BEGIN
  SELECT test_seq.NEXTVAL
  INTO :NEW.id_korisnika
  FROM DUAL;
END;
/

If I start from begining everything works great, I have numbers as 1,2,3,4.... I close the program, open it again, so oracle database connection is once again started. I add one more input and I have numbers like 20,21,22,23... I put program on my android and connect from different device, when I input one user I have 30,31,33,34...

Why is this happening? And how to fix it?

Thank you

EDIT:

Here is my proc for reading data from database

CREATE OR REPLACE PROCEDURE Citanje_korisnika( p_rc OUT SYS_REFCURSOR )
AS
BEGIN
  OPEN p_rc
   FOR SELECT *
         FROM Korisnici;
END;

I am a bit of newbie in oracle database.

回答1:

Specifying the SEQUENCE with NOCACHE will stop a session caching 20 numbers at a time and help.

create sequence test_seq
start with 1 
increment by 1
NOCACHE;

However, if you're hoping for a completely contiguous sequence this is very difficult to achieve - numbers taken from the sequence are "lost" if (for example) an insert is rolled back.

---

Based on your comment, I wonder if you're forgetting to COMMIT?

回答2:

It's not a problem. You have probably specified cache 10 in your sequence creation script. If you change this to nocache it will help with the gaps, at the cost of a hit to performance, but they'll never go away completely as any rollbacks done, and killed inserts etc will use up values. Please see this Ask Tom post.

At the end of the day it shouldn't matter in the slightest. If you're relying on an unbroken sequence as the key of your table then you probably have a problem with your data rather than sequences.