Solved

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

Posted on 2011-09-14
12
3,933 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
  • 6
  • 2
  • 2
  • +2
12 Comments
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
RESTART

 isn't an option.

0
 

Author Comment

by:abuyusuf35
Comment Utility
So how do I move the sequence number back ?
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
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
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

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

Expert Comment

by:sdstuber
Comment Utility
already suggested  http:#36538387
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Ah yes.  Missed it in all the clutter.  Sorry.
0
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
drop it, and recreate it with minvalue=12
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
HainKurt,  http:#36538845


that was already suggested

twice


0
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
sorry :) I did not read all posts, just scanned and missed that part...
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now