Example: The SCIENTIFIC_NAME_SEQ is way out of whack and needs to be incremented.  If the sequence needs to be decreased, it must be dropped & recreated - see Drop & recreate sequences when need to decrease value for instructions


  1. Determine what the next sequence should be:
    SQL> SELECT MAX(SCIENTIFIC_NAME_ID) FROM SCIENTIFIC_NAME;
    MAX(SCIENTIFIC_NAME_ID):242065
  2. Determine what it is currently set to:
    SQL> SELECT last_number FROM user_sequences WHERE sequence_name = 'SCIENTIFIC_NAME_SEQ';
    LAST_NUMBER: 234181
  3. Update the incremental value to set it to the desired value (242066) by subtracting the current value and incrementing by the resulting amount:
    SQL> ALTER SEQUENCE SCIENTIFIC_NAME_SEQ INCREMENT BY 7886;
  4. Verify it's set correctly:
    SQL> SELECT SCIENTIFIC_NAME_SEQ.NEXTVAL FROM dual;
    NEXTVAL: 242067
  5. Update the increment value to 1:
    SQL> ALTER SEQUENCE SCIENTIFIC_NAME_SEQ INCREMENT BY 1;