ORA-08004 Sequence exceed MAXVALUE

ORA-08004 Sequence exceed MAXVALUE

Recently I faced a problem on one of our applications, users were getting an error message after login.

All of our monitoring tools showed no problem on the server, nor on the database.

By taking a look in the logs, I find an oracle error that comes up very often

Erreur oracle

Indeed, when we look directly in the database :

select * from dba_sequences where sequence_name like '%SEQ_ADM%';

sequence oracle

We noticed that the sequence has reached its maximum occurrence

A little reminder on the sequences:

Sequences are database objects from which multiple users can generate unique integers. The sequence generator generates sequential numbers, which can help to generate unique primary keys automatically, and to coordinate keys across multiple rows or tables.

From now on we have several solutions :

  • We can reset the sequence
  • We can increase the MAX_VALUE
  • We can enable the Cycle option

The sequence incriminated here is closely linked to the application, so I chose to increase the MAX_VALUE parameter.

We can easily increase the limit by this command :

alter sequence SCOTT.SEQ_ADM_CONNECTED_PARAM MAXVALUE 11000000;

We can check that the parameter has been taken into account:

select * from dba_sequences where sequence_name like '%SEQ_ADM_CONNECTED%';

verification des parametres

The parameter is taken into account directly and the application is accessible again.

A little later, I ended up activating the Cycle option on this sequence

ALTER SEQUENCE SCOTT.SEQ_ADM_CONNECTED_PARAM CYCLE;

Leave a Reply

Your email address will not be published. Required fields are marked *