Forum Stats

  • 3,759,068 Users
  • 2,251,495 Discussions
  • 7,870,480 Comments

Discussions

how to increase the sequence values via a Script

Satyam Reddy
Satyam Reddy Member Posts: 296 Bronze Badge

Team,

Per our project requirement , we are supposed to increase the sequence last number with value 500. Please suggest me how it can be done via a script with existing cache ,order,cycle values.

I normally take the create script from user_sequences table with the script shown below :

--Sequence creatioin script
SELECT  'CREATE SEQUENCE '
    || sequence_name
    || ' MINVALUE '
    || min_value
    || ' MAXVALUE '
    || max_value
    || ' INCREMENT BY '
    || increment_by
    || ' START WITH '
    || (last_number + 500)
    || ' NOCACHE '
    || ' NOCYCLE '
 || ';'
 FROM user_sequences;

--Drop script:
select 'DROP SEQUENCE '||sequence_name||';' from USER_SEQUENCES;

Then drop all the sequence and recreate the sequences using the above script.

But i could not successfully load the cache, order and cycle values.

Appreciate Any recommendations

Tagged:

Best Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,740 Black Diamond
    Accepted Answer

    Dropping sequence will result in losing all grants on that sequence and invalidation of dependent objects. If all you need to bump up current value by 500 just issue:

    DECLARE
        V_VAL NUMBER;
        CURSOR V_SEQ_CUR
          IS
            SELECT  SEQUENCE_NAME
              FROM  USER_SEQUENCES;
    BEGIN
        FOR V_REC IN V_SEQ_CUR LOOP
          FOR V_I IN 1..500 LOOP
            EXECUTE IMMEDIATE 'SELECT "' || V_REC.SEQUENCE_NAME || '".NEXTVAL FROM DUAL' INTO V_VAL;
          END LOOP;
        END LOOP;
    END;
    /
    

    SY.

    Satyam Reddy
  • Satyam Reddy
    Satyam Reddy Member Posts: 296 Bronze Badge
    Accepted Answer

    Hi Yakobson,

    Great approach from to bump sequence values by 500, but hope this would not work in real time where i need to increment sequence values by some 10k or more.

    Appreciate your approach , but any other approach would be helpful.

    Thank You !!

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,060 Red Diamond
    Accepted Answer

    Hi, @[email protected]

     I want a script which will increase only the last_number values of all sequences with 500.

    Are you saying you don't want a script that drops and re-creates sequences? In that case, do what Solomon suggested, or something equivalent.

    If you really do want a script that drops and re-creates sequences (losing grants, as Solomon warned), then all the information you need is in user_sequences, in particular in the columns you mentioned:

    CYCLE_FLAG

    ORDER_FLAG

    CACHE_SIZE

    Once again, what exactly is the problem? Post your best attempt, and ask a specific question, such as "How can I output  NOCACHE when cache_size = 0, and  CACHE x when cache_size = x (when x is greater than 0)?" (If that happens to be your question the answer is "Use a CASE expression".)

    Satyam Reddy
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,740 Black Diamond
    Accepted Answer

    Good point. If we don't need exact offset and are ok with last cached out number + offset:

    , soSQL> SELECT SEQ.NEXTVAL FROM DUAL
      2  /
    
       NEXTVAL
    ----------
           741
    
    SQL> DECLARE
      2      CURSOR V_SEQ_CUR
      3        IS
      4          SELECT  SEQUENCE_NAME,
      5                  LAST_NUMBER
      6            FROM  USER_SEQUENCES;
      7  BEGIN
      8      FOR V_REC IN V_SEQ_CUR LOOP
      9        EXECUTE IMMEDIATE 'ALTER SEQUENCE "' || V_REC.SEQUENCE_NAME || '" RESTART START WITH ' || (V_REC.LAST_NUMBER + 500);
     10      END LOOP;
     11  END;
     12  /
    
    PL/SQL procedure successfully completed.
    
    SQL>
    
    PL/SQL procedure successfully completed.
    
    SQL> SELECT SEQ.NEXTVAL FROM DUAL
      2  /
    
       NEXTVAL
    ----------
          1261
    
    
    SQL>
    

    If we do want exact:

    SQL> SELECT SEQ.NEXTVAL FROM DUAL
      2  /
    
    
       NEXTVAL
    ----------
          1788
    
    
    SQL> DECLARE
      2      V_VAL NUMBER;
      3      CURSOR V_SEQ_CUR
      4        IS
      5          SELECT  SEQUENCE_NAME
      6            FROM  USER_SEQUENCES;
      7  BEGIN
      8      FOR V_REC IN V_SEQ_CUR LOOP
      9        EXECUTE IMMEDIATE 'SELECT "' || V_REC.SEQUENCE_NAME || '".NEXTVAL FROM DUAL' INTO V_VAL;
     10        EXECUTE IMMEDIATE 'ALTER SEQUENCE "' || V_REC.SEQUENCE_NAME || '" RESTART START WITH ' || (V_VAL + 499);
     11      END LOOP;
     12  END;
     13  /
    
    
    PL/SQL procedure successfully completed.
    
    
    SQL> SELECT SEQ.NEXTVAL FROM DUAL
      2  /
    
    
       NEXTVAL
    ----------
          2288
    
    
    SQL>
    

    SY.

    Satyam Reddy
«1

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,060 Red Diamond

    Hi, @[email protected]

    But i could not successfully load the cache, order and cycle values.

    What exactly is the problem? Show how you tried, using the cache_size, order_flag and cycle_flag columns from user_sequences.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,740 Black Diamond
    Accepted Answer

    Dropping sequence will result in losing all grants on that sequence and invalidation of dependent objects. If all you need to bump up current value by 500 just issue:

    DECLARE
        V_VAL NUMBER;
        CURSOR V_SEQ_CUR
          IS
            SELECT  SEQUENCE_NAME
              FROM  USER_SEQUENCES;
    BEGIN
        FOR V_REC IN V_SEQ_CUR LOOP
          FOR V_I IN 1..500 LOOP
            EXECUTE IMMEDIATE 'SELECT "' || V_REC.SEQUENCE_NAME || '".NEXTVAL FROM DUAL' INTO V_VAL;
          END LOOP;
        END LOOP;
    END;
    /
    

    SY.

    Satyam Reddy
  • Satyam Reddy
    Satyam Reddy Member Posts: 296 Bronze Badge

    Hi,

    I want to get the existing sequences values from the script shared above.

    CYCLE_FLAG
    ORDER_FLAG
    CACHE_SIZE
    

    In other words I want a script which will increase only the last_number values of all sequences with 500.


    Thank You !!

  • Satyam Reddy
    Satyam Reddy Member Posts: 296 Bronze Badge
    Accepted Answer

    Hi Yakobson,

    Great approach from to bump sequence values by 500, but hope this would not work in real time where i need to increment sequence values by some 10k or more.

    Appreciate your approach , but any other approach would be helpful.

    Thank You !!

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,060 Red Diamond
    Accepted Answer

    Hi, @[email protected]

     I want a script which will increase only the last_number values of all sequences with 500.

    Are you saying you don't want a script that drops and re-creates sequences? In that case, do what Solomon suggested, or something equivalent.

    If you really do want a script that drops and re-creates sequences (losing grants, as Solomon warned), then all the information you need is in user_sequences, in particular in the columns you mentioned:

    CYCLE_FLAG

    ORDER_FLAG

    CACHE_SIZE

    Once again, what exactly is the problem? Post your best attempt, and ask a specific question, such as "How can I output  NOCACHE when cache_size = 0, and  CACHE x when cache_size = x (when x is greater than 0)?" (If that happens to be your question the answer is "Use a CASE expression".)

    Satyam Reddy
  • Satyam Reddy
    Satyam Reddy Member Posts: 296 Bronze Badge
  • User_H3J7U
    User_H3J7U Member Posts: 485 Bronze Trophy

    need to increment sequence values by some 10k or more

    Alter increment by 10k, nextval, alter increment backward. But if at this time someone gets nextval...

  • EdStevens
    EdStevens Member Posts: 28,467 Gold Crown

    Great approach from to bump sequence values by 500, but hope this would not work in real time where i need to increment sequence values by some 10k or more.

    Whenever someone rejects a solution based on some vague "hope", then I really start to question the original "requirement".

    Exactly why does the sequence need to be increased by 500? Or is it 10k? Or is it more than 10k? Why do we not know the correct value? What business problem is increasing the current value of the sequence supposed to accomplish?

    Frank Kulash
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,756 Gold Crown
    edited Aug 11, 2021 8:05PM

    Which version of Oracle ?

    SQL> select s1.nextval from dual;
    
       NEXTVAL
    ----------
             2
    
    1 row selected.
    
    SQL> alter sequence s1 restart start with 502;
    
    Sequence altered.
    
    SQL> select s1.nextval from dual;
    
       NEXTVAL
    ----------
           502
    
    1 row selected.
    

    I'll leave it to you to decide how you want to find the current value of the sequence and pass it into the call to restart the sequence. The method should be good for 18c and 19c, and restart does exist in 12.1 but isn't documented.

    The restart leaves the minvalue unchanged, so if you do another restart the sequence will start at minvalue, not the current "start with".

    Regards

    Jonathan Lewis


    P.S. I should have mentioned that this is one of the details in the fourth article in a series on sequences that I've written for SimpleTalk.

    Checking the draft, I find I'd forgotten that there's a bug in the base 12.1.0.2 version that means it won't work without the patch for bug 19602919 - which suggests that even though it's not documented in that version it seems to be supported.

    Frank Kulash
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,740 Black Diamond

    10K is not an issue - sequences are fast. And this is not something you'd run daily (if you do - then most likely there is design flaw).

    SY.