1 ACCEPTED SOLUTION
Accepted Solutions
Hi Keir,
I am assuming you are using caching in your sequence and that this is why numbers are getting skipped. While caching can increase performance this is a definite draw back.
Here is an article we have on removing cache from sequences where needed: https://www.ptc.com/appserver/cs/view/solution.jsp?n=CS113612
Please pay attention to the disclaimer in the article and use this oracle reference documentation to ensure the safety of your database: http://docs.oracle.com/cd/B28359_01/server.111/b28310/views002.htm#ADMIN11803
"Choosing a high value for CACHE lets you access more successive sequence numbers with fewer reads from disk to the sequence cache. However, if there is an instance failure, then all sequence values in the cache are lost. Cached sequence numbers also could be skipped after an export and import if transactions continue to access the sequence numbers while the export is running.
If you use the NOCACH option in the CREATE SEQUENCE statement, then the values of the sequence are not stored in the sequence cache. In this case, every access to the sequence requires a disk read. Such disk reads slow access to the sequence."
Hopefully others can weigh in with their success with this method! 
Thanks,
Jarrett
Hi Keir,
I am assuming you are using caching in your sequence and that this is why numbers are getting skipped. While caching can increase performance this is a definite draw back.
Here is an article we have on removing cache from sequences where needed: https://www.ptc.com/appserver/cs/view/solution.jsp?n=CS113612
Please pay attention to the disclaimer in the article and use this oracle reference documentation to ensure the safety of your database: http://docs.oracle.com/cd/B28359_01/server.111/b28310/views002.htm#ADMIN11803
"Choosing a high value for CACHE lets you access more successive sequence numbers with fewer reads from disk to the sequence cache. However, if there is an instance failure, then all sequence values in the cache are lost. Cached sequence numbers also could be skipped after an export and import if transactions continue to access the sequence numbers while the export is running.
If you use the NOCACH option in the CREATE SEQUENCE statement, then the values of the sequence are not stored in the sequence cache. In this case, every access to the sequence requires a disk read. Such disk reads slow access to the sequence."
Hopefully others can weigh in with their success with this method!
Thanks,
Jarrett
