Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Vince Crismer.

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

Answered by: Tom Kyte - Last updated: October 27, 2020 - 1:38 am UTC

Category: Database - 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 we 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)

and you rated our response

  (29 ratings)

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

Reviews

Sequences

June 08, 2002 - 7:04 pm UTC

Reviewer: Monz from Reston, USA

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?




Tom Kyte

Followup  

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

June 09, 2002 - 2:24 pm UTC

Reviewer: Doug from Danbury, CT USA

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

6 months early!

June 10, 2002 - 9:48 am UTC

Reviewer: Santa's helper from like a rat in a cage I am trapped inside the cubicle

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

follow up

June 28, 2002 - 7:46 pm UTC

Reviewer: ramesh Konda from Minnesota

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 ****** 

Tom Kyte

Followup  

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

February 02, 2003 - 8:47 pm UTC

Reviewer: Ajeet from cincinnati

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



Tom Kyte

Followup  

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.

June 25, 2003 - 10:03 am UTC

Reviewer: A reader


February 07, 2004 - 3:55 am UTC

Reviewer: Prince from Pakistan

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 :)

Tom Kyte

Followup  

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.

May 13, 2004 - 12:02 pm UTC

Reviewer: Sujit Mondal from NY , USA

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

Tom Kyte

Followup  

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

May 28, 2004 - 8:40 am UTC

Reviewer: Jason from Ontario, Canada

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?

Tom Kyte

Followup  

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

November 24, 2004 - 12:42 am UTC

Reviewer: shyam from india

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

reset all sequences

May 17, 2005 - 11:14 am UTC

Reviewer: A reader

hi

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

Thanks


Tom Kyte

Followup  

May 17, 2005 - 2:12 pm UTC

there isn't any way.

user's env

May 17, 2005 - 2:22 pm UTC

Reviewer: A reader

In a schema ?

Tom Kyte

Followup  

May 17, 2005 - 2:40 pm UTC

one at a time.

Tom's code from another post

May 17, 2005 - 2:56 pm UTC

Reviewer: Jeff from Atlanta

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;
/



Tom Kyte

Followup  

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

May 17, 2005 - 3:46 pm UTC

Reviewer: andrew from ca, usa

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.
 

Tom Kyte

Followup  

May 17, 2005 - 4:21 pm UTC

looks like you did then.

Tom Kyte's patience

May 17, 2005 - 3:56 pm UTC

Reviewer: Karthik Kirubakaran from Connecticut, USA

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

Tom Kyte

Followup  

May 17, 2005 - 4:22 pm UTC

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

Resetting Sequences

October 15, 2008 - 3:45 pm UTC

Reviewer: MK from London, UK

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!
Tom Kyte

Followup  

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.

March 18, 2009 - 12:59 am UTC

Reviewer: George Rypysc from Connecticut, USA

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

October 09, 2009 - 6:32 am UTC

Reviewer: A reader

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

November 09, 2010 - 11:22 pm UTC

Reviewer: fagri

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?
Tom Kyte

Followup  

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

November 16, 2010 - 9:23 pm UTC

Reviewer: Barbara Boehmer from Riverside County, California, U.S.A.

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>




 

Tom Kyte

Followup  

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

November 17, 2010 - 11:04 am UTC

Reviewer: Barbara Boehmer from Riverside County, California, U.S.A.

Tom,

Thanks for the detailed explanation.

Regards,
Barbara

it's documented

November 17, 2010 - 12:41 pm UTC

Reviewer: Sokrates from DOAG N├╝rnberg beside the unconference room

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"
Tom Kyte

Followup  

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

June 22, 2011 - 10:34 am UTC

Reviewer: Jaroslav Tachovsky

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

Followup  

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

June 29, 2011 - 3:42 pm UTC

Reviewer: jaime from chile

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

Followup  

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.

June 30, 2011 - 2:09 am UTC

Reviewer: Sokrates

the update would not be correct, either:

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

Followup  

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

July 01, 2011 - 4:44 am UTC

Reviewer: venkata from Bangalore-INDIA

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.
Tom Kyte

Followup  

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

March 05, 2013 - 7:28 am UTC

Reviewer: Bal from Belgium

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

July 02, 2013 - 10:22 am UTC

Reviewer: Miquel Coll from Mallorca, Spain

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

October 23, 2020 - 2:21 pm UTC

Reviewer: fnds from VA USA

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
Connor McDonald

Followup  

October 27, 2020 - 1:38 am UTC

True - we'll update the original question answer.

More to Explore

Administration

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