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
Run the following queries/statements while logged into SQLPlus, SQL Developer, Tora, or Toad as the biotics_dlink user:
- Determine what the next sequence should be:
SQL> SELECT MAX(SCIENTIFIC_NAME_ID) FROM SCIENTIFIC_NAME;
MAX(SCIENTIFIC_NAME_ID):242065 - Determine what it is currently set to:
SQL> SELECT last_number FROM user_sequences WHERE sequence_name = 'SCIENTIFIC_NAME_SEQ';
LAST_NUMBER: 234181 - 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; - Verify it's set correctly:
SQL> SELECT SCIENTIFIC_NAME_SEQ.NEXTVAL FROM dual;
NEXTVAL: 242067 - Update the increment value to 1:
SQL> ALTER SEQUENCE SCIENTIFIC_NAME_SEQ INCREMENT BY 1;