[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5064
  • Last Modified:

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

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
abuyusuf35
Asked:
abuyusuf35
  • 6
  • 2
  • 2
  • +2
1 Solution
 
sdstuberCommented:
RESTART

 isn't an option.

0
 
abuyusuf35Author Commented:
So how do I move the sequence number back ?
0
 
sdstuberCommented:
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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
Devinder Singh VirdiCommented:
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
 
slightwv (䄆 Netminder) Commented:
Can I assume dropping and recreating it with the proper starting number isn't an option?
0
 
sdstuberCommented:
already suggested  http:#36538387
0
 
slightwv (䄆 Netminder) Commented:
Ah yes.  Missed it in all the clutter.  Sorry.
0
 
HainKurtSr. System AnalystCommented:
drop it, and recreate it with minvalue=12
0
 
sdstuberCommented:
HainKurt,  http:#36538845


that was already suggested

twice


0
 
HainKurtSr. System AnalystCommented:
sorry :) I did not read all posts, just scanned and missed that part...
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 6
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now