Search the Site

Sponsors

bottom corner

Manually incrementing an Oracle sequence by a defined amount

This note shows how you can instruct Oracle to advance an Oracle sequence by an amount that you define.

This page is filed under keyword(s): oracle.

Let's say you have a sequence object created called myschema.seq_mysequence which has the next value of 3000 and it normally increments 1 at a time, and let's say that you need to reserve all numbers between 3000 and 4000 for a special purpose, thus you need to manually advance the sequence from 3000 to 4000. This is how you can increment the sequence 1000:

-- First, alter the object so the next increment will jump 1000 instead of just 1.
alter sequence myschema.seq_mysequence increment by 1000;

-- Run a select to actually increment it by 1000
select myschema.seq_mysequence.nextval from dual;

-- Alter the object back to incrementing only 1 at a time
alter sequence myschema.seq_mysequence increment by 1;

Did you find this page useful? Please consider browsing other articles or subscribing to the RSS feed to keep up with latest.

This page is filed under keyword(s): oracle.
Author: C. Peter Chen
Last updated: 5 May 2010

bottom corner