Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-09-14
12
Medium Priority
?
4,884 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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 2000 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 60

Expert Comment

by:HainKurt
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 60

Expert Comment

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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
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…

618 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