Wednesday, March 11, 2015

Oracle updating sequence by a big number

sometimes in DB refresh , the rows are refreshed but corresponding sequences are not refreshed. and the difference may be as huge as 2000-3000. So how can we update the Sequence ?

  1. Can use alter sequence : to modify start value ( not good as different environments will have different definitions)
  2. can write a small java program that selects nextVal from sequence in a for loop 2k-3k
  3. Else can use this
select  level, sequence.NEXTVAL
from  dual 
connect by level <= (select max(pk) from tbl);

No comments: