Solved

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

Posted on 2011-09-14
12
4,053 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
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 73

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
 
LVL 73

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 73

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
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)
ID: 36538561
Can I assume dropping and recreating it with the proper starting number isn't an option?
0
 
LVL 73

Expert Comment

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

Expert Comment

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

Expert Comment

by:HainKurt
ID: 36538845
drop it, and recreate it with minvalue=12
0
 
LVL 73

Expert Comment

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


that was already suggested

twice


0
 
LVL 51

Expert Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle -- identify blocking session 24 43
PAYER_ID has both atributes 4 31
history tablespace temp usage 2 31
1 FROM DUAL wont work with additional columns ?? 4 25
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…
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.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
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.

920 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

14 Experts available now in Live!

Get 1:1 Help Now