Tip: Reset Oracle Sequence

Yesterday I was working on an ODI interface, a pretty easy one, which was supposed to insert new rows  into a table using a <SEQUENCE>.NEXTVAL for populating the primary key.
The primary key was set NUMBER(4). The last value inserted had 17 as primary key. I was expecting new items to start from 18, but I was constantly getting: 

SQL Error: ORA-12899: value too large for column

After spending  a considerable amount of time for finding out which column was making my ODI interface fail, I have executed the following: 

SELECT MY_SEQ.NEXTVAL

FROM DUAL; 

which returned me: 124586!!!

Something has obviously gone wrong with that sequence and I needed to restore it to 17. So basically I have executed the following: 

ALTER SEQUENCE MY_SEQ INCREMENT BY -(124586-17)

and then execute: 

SELECT MY_SEQ.NEXTVAL

FROM DUAL; 

to restore MY_SEQ.CURRVAL to 17. 

Then you reset the correct increment by executing: 

  

 ALTER SEQUENCE MY_SEQ INCREMENT BY 1

Tip: Oracle SQL Developer Data Modeler Create Sequence for an auto incrementing ID

Recently I have started using this tool from Oracle and I must admit it is brilliant for designing Star Schemas and generating DDL code.
For my project I need to define an auto incrementing ID field for several tables of my model. I obviously needed a sequence for each and I was looking for a way to define it in the Data Modeler.
Oracle Data Modeler allows you to mark any column field as auto-incrementing. Select the table in your model and double click on the column. In the example below, I want the primary key field to be auto-incrementing.

Select the option Auto-Incrementing in the Column Property panel.

If you check the DDL you will see the code for the sequence and the trigger that is fired whenever a new line is inserted into the table.

CREATE SEQUENCE CNE_CNEIDE_SEQ
    NOCACHE
    ORDER ;

CREATE OR REPLACE TRIGGER CNE_CNEIDE_TRG
BEFORE INSERT ON T_CANAL_ENTRADA
FOR EACH ROW
WHEN (NEW.CNEIDE IS NULL)
BEGIN
    SELECT CNE_CNEIDE_SEQ.NEXTVAL INTO :NEW.CNEIDE FROM DUAL;
END;
/

If you don't need the trigger, just untick the option in the Auto Increment panel.