mercoledì 27 febbraio 2013

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


Nessun commento:

Posta un commento