Skip to Main Content
  • Questions
  • A sequence does not start with the value specified by start with clause

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Xiaohe.

Asked: May 26, 2016 - 9:28 am UTC

Last updated: May 27, 2016 - 6:35 am UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

Hi Sir,

I create a sequence, alter it, and it does not start with the value specified by start with clause, can you please help ?

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

SQL>  create sequence test_seq INCREMENT BY 2 START WITH 100 MAXVALUE 1000 NOCACHE NOCYCLE;

Sequence created.

SQL> alter sequence test_seq INCREMENT BY 1 MAXVALUE 999999 NOCACHE NOCYCLE;

Sequence altered.

SQL> select test_seq.nextval from dual;

   NEXTVAL
----------
        99

SQL> select test_seq.currval from dual;

   CURRVAL
----------
        99

SQL>



If I don't alter it, however, I will get the 100 specified by start with clause.


Thanks,
Xiaohe

and Connor said...

This is alluded to in the documentation:

"If you change the INCREMENT BY value before the first invocation of NEXTVAL, then some sequence numbers will be skipped. Therefore, if you want to retain the original START WITH value, you must drop the sequence and re-create it with the original START WITH value and the new INCREMENT BY value."

But I agree, it's an interesting behaviour...you can magnify the effect as well, for example:


SQL> drop sequence test_Seq;

Sequence dropped.

SQL>
SQL> create sequence test_seq INCREMENT BY 25 START WITH 100 MAXVALUE 1000 NOCACHE NOCYCLE;

Sequence created.

SQL>
SQL> @pt "select * From user_sequences where sequence_name = 'TEST_SEQ'"
SEQUENCE_NAME                 : TEST_SEQ
MIN_VALUE                     : 1
MAX_VALUE                     : 1000
INCREMENT_BY                  : 25
CYCLE_FLAG                    : N
ORDER_FLAG                    : N
CACHE_SIZE                    : 0
LAST_NUMBER                   : 100
-----------------

PL/SQL procedure successfully completed.

SQL>
SQL> alter sequence test_seq INCREMENT BY 1 MAXVALUE 999999 NOCACHE NOCYCLE;

Sequence altered.

SQL>
SQL> @pt "select * From user_sequences where sequence_name = 'TEST_SEQ'"
SEQUENCE_NAME                 : TEST_SEQ
MIN_VALUE                     : 1
MAX_VALUE                     : 999999
INCREMENT_BY                  : 1
CYCLE_FLAG                    : N
ORDER_FLAG                    : N
CACHE_SIZE                    : 0
LAST_NUMBER                   : 76
-----------------

PL/SQL procedure successfully completed.

SQL>
SQL> select test_seq.nextval from dual;

   NEXTVAL
----------
        76

1 row selected.

SQL>
SQL>


Rating

  (2 ratings)

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

Comments

Documentation link

Rajeshwaran, Jeyabal, May 27, 2016 - 1:49 am UTC

Would be cool, if you could post documentation link along with that text, that helps us to remember where this contents is available in documentation section, ( is that quote is from sql-language reference or application developer guide or 2 day application developer section? )
Connor McDonald
May 27, 2016 - 6:35 am UTC

Well...the poster was altering a sequence, so the "ALTER SEQUENCE" documentation would be a logical place to start :-)

http://docs.oracle.com/database/121/SQLRF/statements_2014.htm#SQLRF00817

significance and source ocde of @pt

jagannath sahu, May 27, 2016 - 6:24 am UTC

Hi Tom,
could you explain significance of @pt and provide the source code of @pt ?
Connor McDonald
May 27, 2016 - 6:34 am UTC

@pt is equivalent to the "print_table" facility, used frequently on asktom.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library