Sometimes you may find your sequences jump by a constant value(e.g. 20) and leaving a gap in-between. This is due to the losing of the cached sequence in the shared pool. There are several common reasons for losing the cached sequences,e.g. issuing alter system flush shared_pool and shutdown database with abort options. This often requires DBA to reset the sequence back to the correct value or ,as suggested by Thomas Kyte, reengineer the application ( or even business logic) to handle the problem.
On the other hand, we can create sequence with nocache option(by default sequences are created with caching option on). Note that, for busy OLTP database, setting nocache on may lead to performance problem, particularly in RAC database.
You can check the following links to find out more about this issue.
http://www.jlcomp.demon.co.uk/faq/seq_loss.html
http://dbaforums.org/oracle/index.php?s=1677c76331bcd690ae3ae62a1a3b33d0&showtopic=5156&st=0&p=19575entry19575
http://www.orafaq.com/forum/t/126004/0/

Post a Comment