CODEWITHSUNDEEP
oracle-databasesequence
Nityanand Shet
Nityanand Shet

Reputation: 7

sequence exceeds maxvalue problem in oracle

I am facing an issue with respect to sequence exceeds maxvalue while using sequence during data migration of a large set of data.

How to overcome this issue.

Do I need to recreate the table or sequence max limit can be altered?

Upvotes: 0

Views: 4655

Answers (2)

Beefstu
Beefstu

Reputation: 857

If you are using a sequence as a value in your table to uniquely identify a row you may want to consider an IDENTITY column so that's 1 less object you have to worry about

create table t1( seq_num integer GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL, ... ...

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142788

Create a sequence with MAXVALUE (that's what you have now):

SQL> create sequence seqa maxvalue 3;

Sequence created.

SQL> select seqa.nextval from dual;

   NEXTVAL
----------
         1

SQL> select seqa.nextval from dual;

   NEXTVAL
----------
         2

SQL> select seqa.nextval from dual;

   NEXTVAL
----------
         3

SQL> select seqa.nextval from dual;
select seqa.nextval from dual
*
ERROR at line 1:
ORA-08004: sequence SEQA.NEXTVAL exceeds MAXVALUE and cannot be instantiated

Error, as expected (that's what you also have). So, alter and set nomaxvalue:

SQL> alter sequence seqa nomaxvalue;

Sequence altered.

Now it works again:

SQL> select seqa.nextval from dual;

   NEXTVAL
----------
         4

Upvotes: 1

Related Questions