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
Indeed, when we look directly in the database :
select * from dba_sequences where sequence_name like '%SEQ_ADM%';
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%';
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;