You work for a small manufacturing company as a DBA. The company has various applications to manage the details of its business and customer base. The company has only one sales outlet where the operator updates the SALES table in the sales application with the details when a product is sold. The company has many other applications for various other businesses that use the same database.
The sales operator, while generating a report, noticed that there are some gaps generated in the serial number in the SLNO column during peak hours of business.
On investigating, you find that the SLNO column in the SALES table uses the SLNSEQ sequence to generate the serial numbers when a record is inserted.
During peak hours, you noticed the following:
When the operator entered the sales details the last time, the sequence number was 1056300, but when you checked the sequence as follows, you found a gap of 100 numbers:
SQL> SELECT sequence_name, last_number FROM user_sequences WHERE
sequence_name='SLNSEQ';
SEQUENCE_NAME LAST_NUMBER
------------------------------ -----------
SLNSEQ 1056400
You observed this behavior many times and only during peak hours.
What could be the problem and solution for this?