sequence object disabled after restart

[former_member232292]

Dear All,

    I have a question on the sequence object --

1. For test the load table performance, I created a sequence object --   create sequence load_seq -- to generate id for each load test.

2. At first, it works fine with nextval and currval.

3. Today, since we need to reinitialize all the test environment, so I decide to restart it with value 0. -- So I ran --

    alter sequence load_seq restart with 0;

    -- and hope the next value should be 1 from it.

4. But then all attempts on it failed with same error --

   No more values available in sequence load_seq

5. But I can run "select load_seq.currval",  it's fine. Only retreiving next value with "select load_seq.nextval" or "insert .... select load_seq.nextval" failed.

    I don't know why and the RCA. I can fix it with recreate a new sequence object. But I'd like to know where my mistake is. Please kind help. Thanks in advance for any ideas.

Regards

Eisen

[former_member232292 · Accepted solution]

It's fixed -- it's my fault -- I didn't understand the "restart with" quite well -- it means the next "nextval".. So it can't set to 0 which is less than the default min value 1...

Thanks for all.