How to reset sequences?

Thanks for the question, Vince Crismer.

Asked: December 03, 2000 - 4:53 am UTC

Last updated: October 27, 2020 - 1:38 am UTC

Version: Oracle 8.1.5

Viewed 100K+ times! This question is

You Asked

Sir,

Greetings.
I would just like to know if it is possible to truncate a sequence to reset back to its original starting number? Pls. help me. Thank you. Merry Christmass.

- Vince Crismer C. Villena

and Tom said...

From 12.1 onwards, you can do this:

alter sequence <sequence_name> restart start with 1;

but before that, there is no "alter sequence reset" but you can, by playing with the increment by, reset it. Consider:

ops$tkyte@DEV816> create sequence seq;
Sequence created.

ops$tkyte@DEV816> select seq.nextval from emp;

NEXTVAL
----------
1
2
3
4
5
6
7
8
9
10
11
12
13
14

14 rows selected

ops$tkyte@DEV816> column S new_val inc;
ops$tkyte@DEV816> select seq.nextval S from dual;

S
----------
15

ops$tkyte@DEV816> alter sequence seq increment by -&inc minvalue 0;
old 1: alter sequence seq increment by -&inc minvalue 0
new 1: alter sequence seq increment by - 15 minvalue 0

Sequence altered.

ops$tkyte@DEV816> select seq.nextval S from dual;

S
----------
0

ops$tkyte@DEV816> alter sequence seq increment by 1;

Sequence altered.

ops$tkyte@DEV816>
ops$tkyte@DEV816> select seq.nextval from dual;

NEXTVAL
----------
1

So, by finding out the current value of the sequence and altering the increment by to be negative that number and selecting the sequence once -- we can reset it. Just beware that if others are using the sequence during this time - they (or you) may get

ORA-08004: sequence SEQ.NEXTVAL goes below MINVALUE and cannot be instantiated

until you set the sequence increment back to +1.

This would be preferred to dropping and recreating the sequence which would invalidate any dependent objects (such as triggers/stored procedures and so on)

Rating

(30 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Sequences

Monz, June 08, 2002 - 7:04 pm UTC

Tom:

1. How would dropping the sequence invalidate objects. I have several procedures that do inserts and use sequences and was thinking of dropping and recreating sometimes. Would I get an error? I think I tried it before without a problem? I guess you would if you run the procedure and the sequence was not there?

2. Let us say I want to reset a sequence dynamically based on the maximum number in a column. If the max number is 20, I want start with 21.

Can I use a variable to do the increment like:

select max(col) into v_seq from table;

column S new_val inc;
select seq.nextval S from dual;
alter sequence seq increment by -&inc minvalue 0;
alter sequence seq increment by v_seq;
alter sequence seq increment by 1;

3. Problem is that on one web form I use a seqeunce to show user a unique order number that he wants to see before saving record. If user cancels then I have a gap in my orders saved. Is there a way around it?

June 09, 2002 - 10:42 am UTC

1) You have a procedure. This procedure does an insert. This insert references a sequence. Hence, the procedure is dependent on the sequence. If you drop the sequence -- the procedure will go invalid since an object it depends on no longer exists. If you run the procedure right now, it'll fail to compile. If you create the sequence and then run the procedure, it'll successfully recompile itself automagically. So, if you drop and recreate, it'll just invalidate which is a waste of your computer resources but will not result in an error. 2) No, no it won't. You never select from it after the alters, hence the numbers will never change. You could of course do this in two alters as follows (simple math going on here): ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t (x int); Table created. ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( dbms_random.value(1,99999999) ); 1 row created. ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t; X ---------- 68637521 ops$tkyte@ORA817DEV.US.ORACLE.COM> column inc new_val INC; ops$tkyte@ORA817DEV.US.ORACLE.COM> select max_x-s.nextval+1 inc 2 from ( select max(x) max_x from t ); INC ---------- 68637399 ops$tkyte@ORA817DEV.US.ORACLE.COM> alter sequence s increment by &inc; old 1: alter sequence s increment by &inc new 1: alter sequence s increment by 68637399 Sequence altered. ops$tkyte@ORA817DEV.US.ORACLE.COM> select s.nextval from dual; NEXTVAL ---------- 68637522 ops$tkyte@ORA817DEV.US.ORACLE.COM> alter sequence s increment by 1; Sequence altered. ops$tkyte@ORA817DEV.US.ORACLE.COM> select s.nextval from dual; NEXTVAL ---------- 68637523 ops$tkyte@ORA817DEV.US.ORACLE.COM> update t set x = 2; 1 row updated. ops$tkyte@ORA817DEV.US.ORACLE.COM> select max_x-s.nextval+1 inc 2 from ( select max(x) max_x from t ); INC ---------- -68637521 ops$tkyte@ORA817DEV.US.ORACLE.COM> alter sequence s increment by &inc; old 1: alter sequence s increment by &inc new 1: alter sequence s increment by -68637521 Sequence altered. ops$tkyte@ORA817DEV.US.ORACLE.COM> select s.nextval from dual; NEXTVAL ---------- 3 ops$tkyte@ORA817DEV.US.ORACLE.COM> alter sequence s increment by 1; Sequence altered. ops$tkyte@ORA817DEV.US.ORACLE.COM> select s.nextval from dual; NEXTVAL ---------- 4 3) the way around this is TO NOT WORRY ABOUT GAPS AS GAPS ARE INEVITABLE IN A SEQUENCE. There are three things I am sure of in life: o Death o Taxes o Gaps in my sequences

Neat idea

Doug, June 09, 2002 - 2:24 pm UTC

I always drop and recreate. This is an interesting idea to just increment backwards.

6 months early!

Santa's helper, June 10, 2002 - 9:48 am UTC

Didn't Christmas used to come in December and not June? :-)

follow up

ramesh Konda, June 28, 2002 - 7:46 pm UTC

I tried the following and ran into errors. Please advise. 1* alter sequence s_cust_mgmt_group_cmg_id increment by -&inc minvalue SQL> / Enter value for inc: 0 old 1: alter sequence s_cust_mgmt_group_cmg_id increment by -&inc minvalue new 1: alter sequence s_cust_mgmt_group_cmg_id increment by -0 minvalue alter sequence s_cust_mgmt_group_cmg_id increment by -0 minvalue * ERROR at line 1: ORA-01722: invalid number SQL> alter sequence s_cust_mgmt_group_cmg_id increment by -&inc minvalue 0 2 / Enter value for inc: 0 old 1: alter sequence s_cust_mgmt_group_cmg_id increment by -&inc minvalue 0 new 1: alter sequence s_cust_mgmt_group_cmg_id increment by -0 minvalue 0 alter sequence s_cust_mgmt_group_cmg_id increment by -0 minvalue 0 * ERROR at line 1: ORA-04002: INCREMENT must be a non-zero integer SQL> ed Wrote file afiedt.buf 1* alter sequence s_cust_mgmt_group_cmg_id increment by -&inc minvalue 1 SQL> / Enter value for inc: 1 old 1: alter sequence s_cust_mgmt_group_cmg_id increment by -&inc minvalue 1 new 1: alter sequence s_cust_mgmt_group_cmg_id increment by -1 minvalue 1 Sequence altered. SQL> select s_cust_mgmt_group_cmg_id.nextval from dual; NEXTVAL ---------- 23 ****** It did not set the sequence to initial value ******

June 28, 2002 - 7:50 pm UTC

you missed a zero

old 1: alter sequence s_cust_mgmt_group_cmg_id increment by -&inc minvalue
new 1: alter sequence s_cust_mgmt_group_cmg_id increment by -0 minvalue
alter sequence s_cust_mgmt_group_cmg_id increment by -0 minvalue
*
ERROR at line 1:
ORA-01722: invalid number

after MINVALUE.

and then you tried to set the increment to 0, which just isn't allowed (as evidenced by the ora-4002 error)

You need to read the example above again (slower). We get the next val, change the increment to be that (negative), select once to get it to zero and then reset the increment to +1. You didn't do any of that.

Just use the code I provided:

column S new_val inc;
select seq.nextval S from dual;
alter sequence seq increment by -&inc minvalue 0;
select seq.nextval S from dual;
alter sequence seq increment by 1;
select seq.nextval from dual;

do that, as is, and it'll work as advertised.

How to change START WITH for a sequence

Ajeet, February 02, 2003 - 8:47 pm UTC

Tom- based on the above disucssion --can we change the start with value of sequence too.
?

February 03, 2003 - 7:15 am UTC

just reset the value to be whatever "start with" value you want.

say the sequence was created:

create sequence s;

and you are currently at 1,000 and want the sequence to now "start with 50", just use -950 instead of -1000 when you change the increment by to "reset" it.

A reader, June 25, 2003 - 10:03 am UTC

Prince, February 07, 2004 - 3:55 am UTC

Hi Tom!

I have two databases prod and test. test dababase has been populated by importing one month old dump of prod. Now here is a requirement. we want to see the next values of sequences of test database at least equal to prod database's sequence next values. Can you guide plz... how can it be done?

Thanks as always :)

February 07, 2004 - 3:05 pm UTC

you'd have to goto prod, grab the sequence name and last_number from dba_sequences. Then you can use the technique above in test to reset to zero, change the increment to last_number and select nextval (hence making the sequence "be at" last_number, then change the increment to 1 again and it'll be done. something like this (on test -- table t would be created as select from user_sequences on the other machine and moved over) ops$tkyte@ORA9IR2> create table t ( s_name varchar2(30), last_number number ); Table created. ops$tkyte@ORA9IR2> ops$tkyte@ORA9IR2> create sequence s1 increment by 212321; Sequence created. ops$tkyte@ORA9IR2> create sequence s2 increment by 2352; Sequence created. ops$tkyte@ORA9IR2> ops$tkyte@ORA9IR2> select s1.nextval, s2.nextval from dual; NEXTVAL NEXTVAL ---------- ---------- 1 1 ops$tkyte@ORA9IR2> select s1.nextval, s2.nextval from dual; NEXTVAL NEXTVAL ---------- ---------- 212322 2353 ops$tkyte@ORA9IR2> alter sequence s1 increment by 1; Sequence altered. ops$tkyte@ORA9IR2> alter sequence s2 increment by 1; Sequence altered. ops$tkyte@ORA9IR2> select s1.nextval, s2.nextval from dual; NEXTVAL NEXTVAL ---------- ---------- 212323 2354 ops$tkyte@ORA9IR2> ops$tkyte@ORA9IR2> insert into t values ( 'S1', 523323 ); 1 row created. ops$tkyte@ORA9IR2> insert into t values ( 'S2', 10023 ); 1 row created. ops$tkyte@ORA9IR2> ops$tkyte@ORA9IR2> create or replace procedure reset_sequences 2 as 3 l_dummy number; 4 begin 5 for x in ( select * from t ) 6 loop 7 execute immediate 'select ' || x.s_name || '.nextval from dual' into l_dummy; 8 9 execute immediate 'alter sequence ' || x.s_name || 10 ' increment by -' || (l_dummy-1); 11 12 execute immediate 'select ' || x.s_name || '.nextval from dual' into l_dummy; 13 14 execute immediate 'alter sequence ' || x.s_name || 15 ' increment by ' || x.last_number; 16 17 execute immediate 'select ' || x.s_name || '.nextval from dual' into l_dummy; 18 19 dbms_output.put_line( 'last selected value from ' || x.s_name || ' was ' || l_dummy ); 20 end loop; 21 end; 22 / Procedure created. ops$tkyte@ORA9IR2> ops$tkyte@ORA9IR2> ops$tkyte@ORA9IR2> exec reset_sequences; last selected value from S1 was 523324 last selected value from S2 was 10024 PL/SQL procedure successfully completed.

Set the sequece present value to a particular number.

Sujit Mondal, May 13, 2004 - 12:02 pm UTC

Hi Tom,
Can you please let me know if there is any way , we can set the vaule of a sequence to a particular number.
eg.
select S783_INT_NBC_PGM_CD_DM_ID.nextval FROM DUAL
gives me the result 100275
I want to set the value of S783_INT_NBC_PGM_CD_DM_ID to 101907 so that when I refer the S783_INT_NBC_PGM_CD_DM_ID.nextval
later on I will get 101908.
-Sujit

May 13, 2004 - 3:03 pm UTC

hmmm, just like in the fashion I set it to zero above?

sure, just change the increment to be the difference between the NEXTVAL and your desired value

select DESIRED_VALE - s.nextval my_new_increment from dual;

alter the sequence to increment by that.

select s.nextval from dual;

alter the sequence to increment by 1

Gaps

Jason, May 28, 2004 - 8:40 am UTC

To avoid gaps, can't you just use currval everytime and only increment the sequence with nextval if and only if it is actually used?

May 28, 2004 - 11:17 am UTC

no.

gaps happen due to cache.
gaps happen due to rollback.
gaps are pretty much unavoidable with sequences and everything else serializes (one transaction at a time)

very good

shyam, November 24, 2004 - 12:42 am UTC

this has given very clear understanding of how to rollback a sequnce value to pevious value

reset all sequences

A reader, May 17, 2005 - 11:14 am UTC

hi

how to reset all sequences in the database in one go.

Thanks

May 17, 2005 - 2:12 pm UTC

there isn't any way.

user's env

A reader, May 17, 2005 - 2:22 pm UTC

In a schema ?

May 17, 2005 - 2:40 pm UTC

one at a time.

Tom's code from another post

Jeff, May 17, 2005 - 2:56 pm UTC

ops$tkyte@ORA920LAP> create or replace procedure set_seq_to( p_name in varchar2,
p_val in number )
2 as
3 l_num number;
4 begin
5 execute immediate 'select ' || p_name ||
6 '.nextval from dual' INTO l_num;
7 execute immediate 'alter sequence ' || p_name ||
8 ' increment by ' || (p_val-l_num-1) ||
9 ' minvalue 0';
10 execute immediate 'select ' || p_name ||
11 '.nextval from dual' INTO l_num;
12 execute immediate 'alter sequence ' || p_name ||
13 ' increment by 1 ';
14 dbms_output.put_line
15 ( 'Sequence ' || p_name || ' is now at ' || p_val );
16 end;
17 /

Procedure created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> drop sequence s;

Sequence dropped.

ops$tkyte@ORA920LAP> create sequence s;

Sequence created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> exec set_seq_to( 's', 10032 );
Sequence s is now at 10032

PL/SQL procedure successfully completed.

ops$tkyte@ORA920LAP> select s.nextval from dual;

NEXTVAL
----------
10032

ops$tkyte@ORA920LAP> exec set_seq_to( 's', 1 );
Sequence s is now at 1

PL/SQL procedure successfully completed.

ops$tkyte@ORA920LAP> select s.nextval from dual;

NEXTVAL
----------
1

Then I guess you could always:

begin
for i in (select sequence_name from user_sequences)
loop
set_seq_to(i.sequnce_name, 1);
end loop;
end;
/

May 17, 2005 - 3:11 pm UTC

right, but that is not an entire database, nor schema 'in one go', that is one at a time (thats what I meant in my head, of course you can loop, but they are discrete independent steps -- maybe I overparsed that one :)

Need to avoid ORA-04002

andrew, May 17, 2005 - 3:46 pm UTC

SQL> CREATE OR REPLACE PROCEDURE set_seq_to (p_name IN VARCHAR2, p_val IN NUMBER) 2 AS 3 l_num NUMBER; 4 BEGIN 5 EXECUTE IMMEDIATE 'select ' || p_name || '.nextval from dual' INTO l_num; 6 EXECUTE IMMEDIATE 'alter sequence ' || p_name || ' increment by ' || 7 (p_val - l_num - 1)|| ' minvalue 0'; 8 EXECUTE IMMEDIATE 'select ' || p_name || '.nextval from dual' INTO l_num; 9 EXECUTE IMMEDIATE 'alter sequence ' || p_name || ' increment by 1 '; 10 DBMS_OUTPUT.put_line ('Sequence ' || p_name || ' is now at ' || p_val); 11 END; 12 / Procedure created. SQL> CREATE SEQUENCE s1 START WITH 1; Sequence created. SQL> EXEC set_seq_to ('S1', 2); BEGIN set_seq_to ('S1', 2); END; * ERROR at line 1: ORA-04002: INCREMENT must be a non-zero integer ORA-06512: at "SOURCE.SET_SEQ_TO", line 6 ORA-06512: at line 1 SQL> DROP SEQUENCE s1; Sequence dropped. SQL> CREATE OR REPLACE PROCEDURE set_seq_to (p_name IN VARCHAR2, p_val IN NUMBER) 2 AS 3 l_num NUMBER; 4 BEGIN 5 EXECUTE IMMEDIATE 'select ' || p_name || '.nextval from dual' INTO l_num; 6 -- Added check for 0 to avoid "ORA-04002: INCREMENT must be a non-zero integer" 7 IF (p_val - l_num - 1) != 0 THEN 8 EXECUTE IMMEDIATE 'alter sequence ' || p_name || ' increment by ' || 9 (p_val - l_num - 1)|| ' minvalue 0'; 10 END IF; 11 EXECUTE IMMEDIATE 'select ' || p_name || '.nextval from dual' INTO l_num; 12 EXECUTE IMMEDIATE 'alter sequence ' || p_name || ' increment by 1 '; 13 DBMS_OUTPUT.put_line ('Sequence ' || p_name || ' is now at ' || p_val); 14 END; 15 / Procedure created. SQL> CREATE SEQUENCE s1 START WITH 1; Sequence created. SQL> EXEC set_seq_to ('S1', 2); Sequence S1 is now at 2 PL/SQL procedure successfully completed.

May 17, 2005 - 4:21 pm UTC

looks like you did then.

Tom Kyte's patience

Karthik Kirubakaran, May 17, 2005 - 3:56 pm UTC

Hi Tom,
I am a great fan of your site, though I have never asked any questions. I enjoy reading it everyday (marked in my rss feed).
I think you seriously deserve appreciation for your patience in answering questions (many of which are really very basic and stupid, I dont understand why people just ask question without doing their homework, they expect you to spoon feed them).
You are great!.
Thanks
Karthik

May 17, 2005 - 4:22 pm UTC

please -- don't call them stupid, no such thing.... really -- we all learn sometime.

Resetting Sequences

MK, October 15, 2008 - 3:45 pm UTC

Hi Tom,
I have a problem where I have a list of tables and a list of sequences. I fill in these tables with data and after doing so I want to reset the sequences to the correct next vals. The sequences usually follow the naming convention of "<table_name>_id_seq" but sometimes the table name is too large causing the sequence name to be invalid, hence the table name portion in the sequence name is truncated. I am just wondering if there was a method of RELATING what sequence gets used by which table in the database? Is there a way to find out exactly which sequence would get used by a table so I can have a function that takes in the table_name, the sequence name and resets the sequences accordingly. Right now I assume the sequence will adher to the naming convention as above, but this works fine for majority of the cases and fails in some instances. Just want a better way of doing this. Hopefully you can suggest a means.

Thanks in advance!

October 17, 2008 - 7:06 pm UTC

... if there was a method of RELATING what
sequence gets used by which table in the database? ...

no, because there is quite simply no relationship between a sequence and a table.

Unless you used a trigger (bad practice in my opinion) to populate the column automagically. Then, the trigger would be dependent in the table and the sequence - giving you a possible *possible* linkage.

but if you did this in some predicable fashion - eg:

sequence_name = substr( table_name, 1, 23 ) || '_id_seq'

then you would be able to do that, assuming you have no name clashes over 23 characters.

There's another way - "re"cycle it.

George Rypysc, March 18, 2009 - 12:59 am UTC

The only problem with "resetting" a sequence by incrementing backwards is, as Tom pointed out, if nextval is called more than once during the brief time when the sequence is set to go backwards, other users (or you) get the error: ORA-08004: sequence SEQ.NEXTVAL goes below MINVALUE and cannot be instantiated A different "reset" technique avoids that error: Set the sequence to "cycle" immediately back to the minvalue you need. And then, if you want, remove the cycle and set maxvalue to a larger value you need for the longterm. For example, to go from 15 back to 3: 10gR2XE> create sequence seq; Sequence created. 10gR2XE> select seq.nextval from emp; NEXTVAL ------------ 1 2 3 4 5 6 7 8 9 10 11 12 13 14 14 rows selected. 10gR2XE> column S new_val max; 10gR2XE> select seq.nextval S from dual; S ------------ 15 10gR2XE> alter sequence seq CYCLE minvalue 3 maxvalue &max cache 10; old 1: alter sequence seq CYCLE minvalue 3 maxvalue &max cache 10 new 1: alter sequence seq CYCLE minvalue 3 maxvalue 15 cache 10 Sequence altered. 10gR2XE> -- Cache had to be set below default of 20 because 10gR2XE> -- this example uses a small sequence range of 10gR2XE> -- 3 to 15 which is smaller than 20. 10gR2XE> -- Otherwise, it would cause the error: 10gR2XE> -- ORA-04013: number to CACHE must be less than one cycle) 10gR2XE> select seq.nextval S from dual; S ------------ 3 10gR2XE> -- Multiple nextval's here cause no error 10gR2XE> -- unlike the incrementing backwards method. 10gR2XE> select seq.nextval S from dual; S ------------ 4 10gR2XE> select seq.nextval S from dual; S ------------ 5 10gR2XE> -- Now, remove the cycle or make it larger 10gR2XE> -- and be sure to increase maxvalue! 10gR2XE> alter sequence seq NOCYCLE maxvalue 9999999 cache 20; Sequence altered. 10gR2XE> select seq.nextval S from dual; S ------------ 6

Oracle sequence VS user created

A reader, October 09, 2009 - 6:32 am UTC

Hi Tom,

Just interested in hearing your thoughts regarding approach
Using Oracle sequence or creating a table and storing the last used number in it and manually incrementing it.

I am curious to know if the option of manually creating sequence has any advantage over Oracle sequences or vice versa.

I would like to know if there is any significant difference between these approaches?

Thank you,
Arch

fagri, November 09, 2010 - 11:22 pm UTC

1-create table x (id number);

2-create sequence x_sq
start with 1
increment by 1;

3-insert into x values (x_sq.NEXTVAL);

when i run this select statement shows the following:

select * from x;

id
-------
2

why this insert doesnt start from 1?

November 11, 2010 - 10:24 am UTC

ops$tkyte%ORA10GR2> create table x (id number); Table created. ops$tkyte%ORA10GR2> ops$tkyte%ORA10GR2> create sequence x_sq 2 start with 1 3 increment by 1; Sequence created. ops$tkyte%ORA10GR2> ops$tkyte%ORA10GR2> insert into x values (x_sq.NEXTVAL); 1 row created. ops$tkyte%ORA10GR2> select * from x; ID ---------- 1

show your work - you did something differently than you say you did.

deferred_segment_creation causes skip in sequence

Barbara Boehmer, November 16, 2010 - 9:23 pm UTC

Tom, The difference between what fagri posted and what you posted by running the same code is due to deferred_segment_creation. With deferred_segment_creation set to the default of true, the sequence skips a number, but with deferred_segment_creation set to false it does not. Please see the code below that demonstrates that. Would you consider this a bug or expected behavior? Would it matter, since we don't expect sequences to be gap-free anyhow? Are there other potential differences due to deferred_segment_creation? Barbara SCOTT@orcl_11gR2> select banner from v$version 2 / BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for 64-bit Windows: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production 5 rows selected. SCOTT@orcl_11gR2> select value from v$system_parameter where name = 'deferred_segment_creation' 2 / VALUE -------------------------------------------------------------------------------- TRUE 1 row selected. SCOTT@orcl_11gR2> create table x (id number) 2 / Table created. SCOTT@orcl_11gR2> create sequence x_sq start with 1 increment by 1 2 / Sequence created. SCOTT@orcl_11gR2> insert into x values (x_sq.NEXTVAL) 2 / 1 row created. SCOTT@orcl_11gR2> select * from x 2 / ID ---------- 2 1 row selected. SCOTT@orcl_11gR2> drop table x 2 / Table dropped. SCOTT@orcl_11gR2> drop sequence x_sq 2 / Sequence dropped. SCOTT@orcl_11gR2> alter system set deferred_segment_creation = false 2 / System altered. SCOTT@orcl_11gR2> select value from v$system_parameter where name = 'deferred_segment_creation' 2 / VALUE -------------------------------------------------------------------------------- FALSE 1 row selected. SCOTT@orcl_11gR2> create table x (id number) 2 / Table created. SCOTT@orcl_11gR2> create sequence x_sq start with 1 increment by 1 2 / Sequence created. SCOTT@orcl_11gR2> insert into x values (x_sq.NEXTVAL) 2 / 1 row created. SCOTT@orcl_11gR2> select * from x 2 / ID ---------- 1 1 row selected. SCOTT@orcl_11gR2>

November 17, 2010 - 7:31 am UTC

... Would you consider this a bug or expected behavior? ...

I don't consider it a bug - as sequences never promise to be gap free.

... Would it matter, since we don't expect sequences to be gap-free anyhow? ...

exactly :)

... Are there other potential differences due to deferred_segment_creation? ...

Actually, under the covers, what you noticed is in fact logged as a bug and fixed - but not because the sequence skipped. Rather, there is an issue (already fixed) whereby a

create table as select ....;

MIGHT run the select twice. The first time, it runs the select, finds there is no extent to start loading into, invoked the deferred segment creation code - but then "forgot" it ran the select and does it again. That could be a bad performance regression.

it is likely the same issue with the insert values on the first row - it is repopulating the row source (which could be a minor performance issue at best with the values clause)

but this is a good reason why posting VERSION INFORMATION is excessively important and relevant.... It would have been helpful if the original poster had told us "version" information.

thanks

Barbara Boehmer, November 17, 2010 - 11:04 am UTC

Tom,

Thanks for the detailed explanation.

Regards,
Barbara

it's documented

Sokrates, November 17, 2010 - 12:41 pm UTC

not only it is not a bug, but it is documented also
http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_6015.htm#SQLRF01314
"...If you attempt to insert a sequence value into a table that uses deferred segment creation, the first value that the sequence returns will be skipped"

November 18, 2010 - 3:15 am UTC

neat, didn't even think to look for that - it wasn't necessary to document it really (as sequences never ever promised to be gap free in the first place).

I have a feeling this will change - I'll have to test it out once I get back.

What about increment 0

Jaroslav Tachovsky, June 22, 2011 - 10:34 am UTC

I wonder, if I use
alter sequence xxx increment 0
and use it to store constant number.

June 22, 2011 - 10:54 am UTC

ops$tkyte%ORA11GR2> create sequence s increment by 0 cycle; create sequence s increment by 0 cycle * ERROR at line 1: ORA-04002: INCREMENT must be a non-zero integer

simply

jaime, June 29, 2011 - 3:42 pm UTC

update all_sequences
set last_value = (select max(key) from table)
where sequence_name='xx'

June 29, 2011 - 5:30 pm UTC

that is an incredibly "not smart" thing to suggest for so many reasons, including

a) all_sequences is a view, not a table. You wouldn't be updating that
b) you don't have the ability (privilege) to update that
c) even if you did, the update is incomplete (forget about the wrong column name - the where clause is incomplete and wrong)
d) and even if you could, it wouldn't work since sequences are cached in the SGA - it would break things
e) you are apparently attempting to update the data dictionary - something *you cannot do*, not without inducing self induced ora-600's and other very odd/bad behavior

In short - do not even consider thinking about doing something so "not smart"

And - before posting advice, you would be well advised to test it out on your own - just like I do here before answering.

This just reminds of of how this entire asktom site got started:

http://asktom.oracle.com/Misc/birth-of-asktom.html

all by someone advising, in a not smart fashion, to update the data dictionary....

and it still happens.... 11 years later...

reason f.

Sokrates, June 30, 2011 - 2:09 am UTC

the update would not be correct, either:

f. "select max(key) from table" would have to be a dirty read query

July 01, 2011 - 8:23 am UTC

or, since we do not do dirty reads, you would have to lock the table first.

do you need help

venkata, July 01, 2011 - 4:44 am UTC

Tom,

I saw your blog on how asktom was born.really good, just outa curiosity wanna know does anyone help you out in replying to the queries you get daily ? will you be associated with oracle even after you retire.

July 01, 2011 - 9:24 am UTC

Unless the answer includes "I asked <someones name> and they said:" - then you can assume that I answered the question. I'm the only one that types in responses on this site.

will
you be associated with oracle even after you retire.

I hope not :) When I retire - I plan on being retired

Great!, I no longer need to drop any sequences in my application

Bal, March 05, 2013 - 7:28 am UTC

Hi Tom,

I no longer need to drop and re-create the sequences in my application with the required START WITH value, which is what I have been doing until now. I only thought about re-visting my approach after a colleague of mine was asked to change the START WITH value and I was curious how he was doing it since I checked the data dictionary and noticed that he wasn't dropping and re-creating the sequence.

Thanks,
Bal

Problem with created and not queried sequences

Miquel Coll, July 02, 2013 - 10:22 am UTC

Hello Tom, I know it's a very old post but still interesting.
I need to reset all sequences and your procedure works fine, but I have problems with a particular case. I need to reset this sequence:
create sequence seq_temp start with 1 increment by 1 nocache nocycle maxvalue 99999;

to 1. With your procedure, the minvalue gets modified to 0, and that "implies" modifying the original DDL sentence. I cannot later modify the minvalue if the original sequence has to be reseted to 1.
I think the only way is to drop&recreate the sequence.
This is my full log:
miquel@pre11g2> select banner from v$version; BANNER ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production miquel@pre11g2> create sequence seq_temp start with 1 increment by 1 nocache nocycle maxvalue 99999; Sequence created. miquel@pre11g2> select * from user_sequences where sequence_name = 'SEQ_TEMP'; SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY CYC ORD CACHE_SIZE LAST_NUMBER -------------------- ---------- ---------- ------------ --- --- ---------- ----------- SEQ_TEMP 1 99999 1 N N 0 1 miquel@pre11g2> select seq_temp.nextval from user_tables; NEXTVAL ---------- 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 39 rows selected. miquel@pre11g2> select * from user_sequences where sequence_name = 'SEQ_TEMP'; SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY CYC ORD CACHE_SIZE LAST_NUMBER -------------------- ---------- ---------- ------------ --- --- ---------- ----------- SEQ_TEMP 1 99999 1 N N 0 40 miquel@pre11g2> CREATE OR REPLACE PROCEDURE set_seq_to (p_name IN VARCHAR2, p_val IN NUMBER) 2 AS 3 l_num NUMBER; 4 BEGIN 5 EXECUTE IMMEDIATE 'select ' || p_name || '.nextval from dual' INTO l_num; 6 -- Added check for 0 to avoid "ORA-04002: INCREMENT must be a non-zero integer" 7 IF (p_val - l_num - 1) != 0 THEN 8 EXECUTE IMMEDIATE 'alter sequence ' || p_name || ' increment by ' || 9 (p_val - l_num - 1)|| ' minvalue 0'; 10 END IF; 11 EXECUTE IMMEDIATE 'select ' || p_name || '.nextval from dual' INTO l_num; 12 EXECUTE IMMEDIATE 'alter sequence ' || p_name || ' increment by 1 '; 13 DBMS_OUTPUT.put_line ('Sequence ' || p_name || ' is now at ' || p_val); 14 END; 15 / Procedure created. miquel@pre11g2> exec set_seq_to('SEQ_TEMP',1); Sequence SEQ_TEMP is now at 1 PL/SQL procedure successfully completed. miquel@pre11g2> select * from user_sequences where sequence_name = 'SEQ_TEMP'; SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY CYC ORD CACHE_SIZE LAST_NUMBER -------------------- ---------- ---------- ------------ --- --- ---------- ----------- SEQ_TEMP 0 99999 1 N N 0 1 miquel@pre11g2> alter sequence SEQ_TEMP minvalue 1; alter sequence SEQ_TEMP minvalue 1 * ERROR at line 1: ORA-04007: MINVALUE cannot be made to exceed the current value miquel@pre11g2> select SEQ_TEMP.currval from dual; CURRVAL ---------- 0

New way on version 12.1

fnds, October 23, 2020 - 2:21 pm UTC

Oracle 12.1 supports a simpler way to reset the sequence:

alter sequence <sequence_name> restart start with 1;

More details here:

https://stackoverflow.com/questions/51470/how-do-i-reset-a-sequence-in-oracle/19673327#19673327

October 27, 2020 - 1:38 am UTC

True - we'll update the original question answer.

A reader, January 13, 2021 - 10:40 am UTC

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Sequences

Neat idea

6 months early!

follow up

How to change START WITH for a sequence

Set the sequece present value to a particular number.

Gaps

very good

reset all sequences

user's env

Tom's code from another post

Need to avoid ORA-04002

Tom Kyte's patience

Resetting Sequences

There's another way - "re"cycle it.

Oracle sequence VS user created

deferred_segment_creation causes skip in sequence

thanks

it's documented

What about increment 0

simply

reason f.

do you need help

Great!, I no longer need to drop any sequences in my application

Problem with created and not queried sequences

New way on version 12.1

More to Explore

Administration