Solved

SQL Error: ORA-02286: no options specified for ALTER SEQUENCE

Posted on 2011-09-14
12
4,556 Views
Last Modified: 2012-05-12
trying to update sequences using

ALTER SEQUENCE SEQ_NAME_SEQ RESTART WITH 12;
ALTER SEQUENCE SEQ_NAME_SEQ INCREMENT BY 1;

and I get SQL Error: ORA-02286: no options specified for ALTER SEQUENCE
0
Comment
Question by:abuyusuf35
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
  • 2
  • +2
12 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 36538376
RESTART

 isn't an option.

0
 

Author Comment

by:abuyusuf35
ID: 36538382
So how do I move the sequence number back ?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36538387
the allowed options are...

INCREMENT BY
MAXVALUE  
MINVALUE
CYCLE
CACHE
ORDER

and the corresponding "NO" versions (NOMAXVALUE, NOMINVALUE, NOCYCLE, NOCACHE,NOORDER)

if you want to loop back to some other value,  drop and recreate the sequence
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 74

Expert Comment

by:sdstuber
ID: 36538405
or you can use a series of ALTER commands


first alter to create a negative increment,
then select to apply that increment
then change your increment back to 1


here is a procedure that will allow you to reset any sequence
CREATE OR REPLACE PROCEDURE set_sequence(
    p_sequence   IN VARCHAR2,
    p_value      IN INTEGER,
    p_owner      IN VARCHAR2 DEFAULT USER
)
IS
    v_increment INTEGER;
    v_dummy     INTEGER;
    v_sql       VARCHAR2(1000);
BEGIN
    SELECT increment_by
    INTO v_increment
    FROM all_sequences
    WHERE sequence_name = p_sequence AND sequence_owner = p_owner;

    v_sql := 'select ' || p_owner || '.' || p_sequence || '.nextval from dual';

    DBMS_OUTPUT.put_line(v_sql);

    EXECUTE IMMEDIATE v_sql INTO v_dummy;

    v_sql :=
           'ALTER SEQUENCE '
        || p_owner
        || '.'
        || p_sequence
        || ' INCREMENT BY '
        || (p_value - v_dummy - v_increment);

    DBMS_OUTPUT.put_line(v_sql);

    EXECUTE IMMEDIATE v_sql;

    v_sql := 'select ' || p_owner || '.' || p_sequence || '.nextval from dual';

    DBMS_OUTPUT.put_line(v_sql);

    EXECUTE IMMEDIATE v_sql INTO v_dummy;

    v_sql := 'ALTER SEQUENCE ' || p_owner || '.' || p_sequence || ' INCREMENT BY ' || v_increment;

    DBMS_OUTPUT.put_line(v_sql);

    EXECUTE IMMEDIATE v_sql;
END;

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36538469
If you don't want to create the procedure,  the resulting sql would look something like this..

results of the first query determine the increment in the next

-1938 is because my sequence was at 1949,  you'll need to adjust for your sequence

select MY_SEQUENCE.nextval from dual;

ALTER SEQUENCE MY_SEQUENCE INCREMENT BY -1938;

select MY_SEQUENCE.nextval from dual;

ALTER SEQUENCE MY_SEQUENCE INCREMENT BY 1;
0
 
LVL 15

Accepted Solution

by:
Devinder Singh Virdi earned 500 total points
ID: 36538496
You need to adjust INCREMENT BY in order to go back, because there is no restart and decrements by.
In order to go back, your increment will be -1.

i.e.

create sequence temp_dev_1sq start with 100 increment by -1 minvalue 0 maxvalue 2000
select temp_dev_1sq.nextval from dual;

To get desire start value, increment with start_value - currval.
i.e.


alter sequence temp_dev_1sq increment by 1; ---- because our increment is -1, Just for simplicity.
alter sequence temp_dev_1sq increment by -88; --- To get starting position as 12, current value is 100
select temp_dev_1sq.nextval from dual;
alter sequence temp_dev_1sq increment by 1; -- to make it 1 incremental.


0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36538561
Can I assume dropping and recreating it with the proper starting number isn't an option?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36538567
already suggested  http:#36538387
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36538579
Ah yes.  Missed it in all the clutter.  Sorry.
0
 
LVL 55

Expert Comment

by:Huseyin KAHRAMAN
ID: 36538845
drop it, and recreate it with minvalue=12
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36538877
HainKurt,  http:#36538845


that was already suggested

twice


0
 
LVL 55

Expert Comment

by:Huseyin KAHRAMAN
ID: 36538990
sorry :) I did not read all posts, just scanned and missed that part...
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question