title: Oracle Sequence does not increase after nextval
body: <p>Calling Oracle sequence nextval seems irregular. Our business constraint(unique token) is to generate unique number, so we use oracle sequence. We use hibernate spring boot application. But, in this case, we calling nextval by native query (JDBC), so that hibernate management does not take place. But, after some time running the application in production, out unique token get unique constraint exception, as oracle sequence tries to generate same sequence number or it does not increase sequence value after some time. 
  In all our unique token, sequence value difference is like 1 or 2. But when sequence value do not increase (upon nextval), manual sequence increment by 2494 (cache 1000 ORDER).  Our sequence value is ORDER but stuck in some values, need to increment by 2494 to maintain our uniqueness</p>
  
  <p>Our sequence definition is like this: </p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE SEQUENCE SEQ_98090 INCREMENT BY 1 CACHE 1000 ORDER NOCYLE NOPARTITION;
  </code></pre>
  
  <p>To solve this, manually we increase sequence value, so that our unique business constraint (unique token) do not get any unique constraint exception. </p>
  
  <p>Our application runs on multiple servers. </p>
  
  <p>We debug the sequence value from outside, we see that oracle sequence value do not increase. </p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE SEQUENCE SEQ_98090 INCREMENT BY 1 CACHE 1000 ORDER NOCYLE NOPARTITION;
  </code></pre>
  
  <pre class="lang-java prettyprint-override"><code>String queryString = "SELECT " + dbSequence + ".NEXTVAL FROM DUAL";
  Query query = em.createNativeQuery(queryString);
  BigDecimal issuedTokenBin = null;
  try {
      issuedTokenBin = (BigDecimal) query.getSingleResult();
  } catch (Exception ex) {
      log.error("Error :", ex);
  }
  </code></pre>
  
  <p>As we get the sequence from oracle sequence, it should always return incremented value.</p>
  
  <p><a href="https://i.stack.imgur.com/YK0uu.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/YK0uu.jpg" alt="From TID value 3009053444 to next TID 3009091522, last part of unique token i.e. oracle sequence stuck. Unless we manually increase it"></a></p>
  
  <p>From the image:  Oracle sequence got stuck at first selected row (app got unique constraint violation). Then we manually increase it and application got going. last 8 digits of the unique token i.e. 79140 and 81634 comes from oracle sequence. </p>