how to increase the sequence values via a Script

Satyam Reddy · Aug 11, 2021 4:28PM

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

Solomon Yakobson · Aug 11, 2021 4:51PM

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 · Aug 11, 2021 5:08PM

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 · Aug 11, 2021 5:15PM

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".)

Solomon Yakobson · Aug 11, 2021 7:44PM

https://community.oracle.com/tech/developers/discussion/comment/16805189#Comment_16805189

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.

Frank Kulash · Aug 11, 2021 4:42PM

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 · Aug 11, 2021 4:51PM

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 · Aug 11, 2021 4:54PM

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 · Aug 11, 2021 5:08PM

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 · Aug 11, 2021 5:15PM

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 · Aug 11, 2021 5:19PM

https://community.oracle.com/tech/developers/discussion/comment/16805174#Comment_16805174

Hi Frank,

I got my doubt clarified.

Thank you both Frank and Solomon !!

User_H3J7U · Aug 11, 2021 6:03PM

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 · Aug 11, 2021 6:22PM

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?

Jonathan Lewis · Aug 11, 2021 6:28PM

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.

Solomon Yakobson · Aug 11, 2021 7:21PM

https://community.oracle.com/tech/developers/discussion/comment/16805172#Comment_16805172

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.

Forum Stats

Quick Links

Categories

how to increase the sequence values via a Script

Best Answers

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

how to increase the sequence values via a Script

Best Answers

Answers