13 January 2008

How to change start number of an Oracle Sequence

Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.5.0
Connected as sysadm


SQL> create sequence mt_seq
2 start with 1
3 increment by 1;

Sequence created

SQL> select mt_seq.nextval from dual;

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

SQL> alter sequence mt_seq start with 1000;

alter sequence mt_seq start with 1000

ORA-02283: cannot alter starting sequence number

SQL> alter sequence increment by 999;

alter sequence increment by 999

ORA-02277: invalid sequence name

SQL> alter sequence mt_seq increment by 999;

Sequence altered

SQL> select mt_seq.nextval from dual;

NEXTVAL
----------
1000

SQL> alter sequence mt_seq increment by 1;

Sequence altered

SQL> select mt_seq.nextval from dual;

NEXTVAL
----------
1001

SQL> ------
SQL>
SQL>
SQL> drop sequence mt_seq;

Sequence dropped

SQL> create sequence mt_seq
2 start with 1000
3 increment by 1;

Sequence created

SQL> select mt_seq.nextval from dual;

NEXTVAL
----------
1000

SQL>

No comments: