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 run Oracle forms in unix ?'
Hi, @satyam-reddy 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.
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.
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 !!
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 !!
Hi, @satyam-reddy 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".)
NOCACHE
CACHE x
Hi Frank,
I got my doubt clarified. Thank you both Frank and Solomon !!
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...
increment by
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?
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.
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.
undefined (0 Bytes)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>
What's the actual point of the requirement here though? Sequences are supposed to offer you a guaranteed unique (with the assumption it doesn't cycle around) value. They're not guaranteed to be gapless and they're not guaranteed to start from a given point (except when you initially create them) due to caching etc. So, there's little point in increasing a sequence by N values, if you increase it, someone selects the nextval and then the database is bounced, the caching will potentially throw the number out from what you expect anyway. Doesn't seem like a valid "project requirement" to me. In a well designed requirement it would simply state something like "each value of this should be unique" and that's it. Any requirement that states "the value should be incremented by 10K from any previous values before starting" sounds like someone who doesn't know their ar*e from their elbow..
@blushadow someone who doesn't know their ar*e from their elbow.. I use the adjustment of the sequence to the greatest round numbers when cloning the data to test environment. This allows me to visually distinguish between cloned and newly generated values.
Cloning to a test environment wouldn't be part of a "project requirement", that's just a technical requirement for testing. The project requirements typically come from the project managers who have dealt with users to define user requirements. Neither users, nor project managers should be specifying technical requirements.