Learning is not a spectator sport

It’s funny how “workarounds” to problems become so ingrained in the public eye that they quickly become treated as the permanent solution. That might all be well and good, but often that leads us into the trap of becoming blinkered to better solutions that might arrive later down the track.

Here’s a simple example of that. Do an internet search on how to set an Oracle sequence back to (say) 1, and you’ll get plenty of hits. They all describe the same approach which has stood the test of time since Tom Kyte first mentioned it back in December 2000.

AskTom – temporarily set a negative increment and use that to wind the sequence back, then set the increment back to its normal value.

StackOverflow – temporarily set a negative increment and use that to wind the sequence back, then set the increment back to its normal value.

OraTable– temporarily set a negative increment and use that to wind the sequence back, then set the increment back to its normal value.

DBGeek– temporarily set a negative increment and use that to wind the sequence back, then set the increment back to its normal value.

Broadcomm– temporarily set a negative increment and use that to wind the sequence back, then set the increment back to its normal value.

ITECNote– temporarily set a negative increment and use that to wind the sequence back, then set the increment back to its normal value.

You can see a pattern emerging here 😀…The list goes on and on…

But that nuisance got fixed waaaayyyy back in 12.1. We simply added a clause to allow you to pick a restart number either in advance of the current value or a lower value. Here’s a simple demo:

SQL> create sequence seq;

Sequence created.

SQL> select seq.nextval from dual;

   NEXTVAL
----------
         1

SQL> select seq.nextval from dual;

   NEXTVAL
----------
         2

SQL> select seq.nextval from dual;

   NEXTVAL
----------
         3

SQL> select seq.nextval from dual;

   NEXTVAL
----------
         4

SQL> alter sequence seq restart minvalue 200;

Sequence altered.

SQL> select seq.nextval from dual;

   NEXTVAL
----------
       200

SQL> select seq.nextval from dual;

   NEXTVAL
----------
       201

SQL> select seq.nextval from dual;

   NEXTVAL
----------
       202

SQL>
SQL>
SQL> alter sequence seq restart minvalue 1;

Sequence altered.

SQL> select seq.nextval from dual;

   NEXTVAL
----------
         1

SQL> select seq.nextval from dual;

   NEXTVAL
----------
         2

SQL> select seq.nextval from dual;

   NEXTVAL
----------
         3

Its a challenge, but our job as IT professionals is to always try keep up to date with our knowledge so we don’t miss out on tech advancements that come our way.

Miss you Joel.