Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

how to increase the sequence values via a Script

Satyam ReddyAug 11 2021

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

This post has been answered by Satyam Reddy on Aug 11 2021
Jump to Answer
Comments
Post Details
Added on Aug 11 2021
14 comments
10,030 views