Change series number in PL/SQL Developer?

Hi @chrisplant,

I also have done this at least twice for customers a few years ago and haven’t heard any complains yet. It’s just a value to be used as the primary key value for the table, so you shouldn’t have any complications as long as the next value generated is unique. 

You can simply run SQL statements like below to achieve this. 

This will set the step size for the next value. Since you want the next value to be 40000 set the value as 20860 as 40000-19139=20861

ALTER SEQUENCE shipment_seq INCREMENT BY 20860;

Now get the next value from the sequence with below. 

SELECT shipment_seq.nextval FROM dual;

This should give you the value as 39999. Once that is verified, set the step size back to 1.

ALTER SEQUENCE shipment_seq INCREMENT BY 1;

Now the next value generated by this sequence will be 40000.

As Joe has mentioned above, make sure to try this on a test environment first and also perform some testing. 

Hope this helps!

You can also drop the sequence and create it anew to set a new starting value, and you don’t need to change the step increment in that case. These are the commands I used in SQL Developer for the PUR_ORDER_NO sequence:

DROP SEQUENCE PUR_ORDER_NO

CREATE SEQUENCE PUR_ORDER_NO MINVALUE 1 MAXVALUE 999999999999 INCREMENT BY 1 START WITH 100000 NOCACHE NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;

COMMIT;

This starts the sequence at 100000.

Doing a DROP and CREATE is obviously ill-advised for a busy database! A new purchase order being added during that time would certainly error out, and the user would probably lose that work. In our case, we are implementing this before going live with purchase orders, so it is safe.

Thanks,

Joe Kaufman