6 Replies Latest reply: Dec 8, 2013 5:09 PM by rp0428 RSS

    oracle sequence

    357b06de-3110-4f11-8e5e-e1e769d4bccb

      we need to reset the sequence number for table once we load the history days for corresponding tables. so that it will pick the Next value of Max value from the table

       

      can someone pls suggest what to do

        • 1. Re: oracle sequence
          Sudhakar_B

          Hi,

          I don;t believe there is any alter sequence... Which version of Oracle?

          This works for me in all versions.

          > drop sequence aseq

          sequence ASEQ dropped.

          > create sequence aseq

          sequence ASEQ created.

          > select aseq.nextval from dual

             NEXTVAL

          ----------

                   1

           

           

          > --I want to set the aseq value to 3. This can be any number!

          > select aseq.nextval from dual connect by level < 4

             NEXTVAL

          ----------

                   2

                   3

                   4

           

           

          > select aseq.currval from dual

             CURRVAL

          ----------

                   4

           

          Hope this help.

          vr,

          Sudhakar

          • 3. Re: oracle sequence
            rp0428
            This works for me in all versions.

            Maybe for the simplest of use cases that will work but that method can have some serious issues.

             

            Try your sample code again when that sequence you are dropping has some dependent objects like functions/procedures/packages on it and see what happens to their status.

             

            Now do it again after you create some grants on that sequence. See what happens to those grants when you drop the sequence.

             

            Then if OP wants to set the sequence value to 9999999999999 it doesn't make a lot of sense to start at 1 and count until you get there. You would either recreate the sequence with the START WITH value you want to use or use the technique in the link Hoek provided and twitch the INCREMENT BY value a couple of times.

            • 4. Re: oracle sequence
              Sudhakar_B

              Oh NO..

              My apologies... I did NOT mean to indicate to DROP or recreate sequences.

              @ OP Please do NOT DROP or RECREATE sequences.

               

              I was only demonstrating that following can be used to increment the sequence value to any target value.

               

              > --I want to set the aseq value to 3. This can be any number!

              > select aseq.nextval from dual connect by level < 4

                 NEXTVAL

              ----------

                       2

                       3

                       4

               

              The DROP, CREATE in my previous post was only for demonstration purpose.

               

              Thanks.

              • 5. Re: oracle sequence
                rp0428
                I did NOT mean to indicate to DROP or recreate sequences.

                Well, that's a relief!

                I was only demonstrating that following can be used to increment the sequence value to any target value.

                Ok - but unless you only need to bump the value by a small number that is not really very efficient.

                 

                And contrary to this:

                I don;t believe there is any alter sequence

                There IS an alter sequence; you just can't use it to set a specific value. But you can 'alter' almost everything else about a sequence:

                http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_2012.htm

                ALTER SEQUENCE

                Purpose

                Use the ALTER SEQUENCE statement to change the increment, minimum and maximum values, cached numbers, and behavior of an existing sequence. This statement affects only future sequence numbers.

                The typical way to bump the sequence value is:

                1. set the INCREMENT BY value so that the 'bump' is the right size

                2. use NEXTVAL to get one value

                3. reset the INCREMENT BY back to the orginal value

                create sequence seq_test

                 

                select seq_test.nextval from dual

                NEXTVAL

                1

                alter sequence seq_test increment by 56789

                 

                select seq_test.nextval from dual

                NEXTVAL

                56790

                The bump size would be the difference between the current value of the sequence (obtained using NEXTVAL) and the value you want the sequence to have. Naturally you need to have ALTER privileges on the sequence or you would have to use a method like you presented.

                • 6. Re: oracle sequence
                  Sudhakar_B

                  Aah...

                  Alter the INCREMENT value... bump it up...and alter it back !  Nice.

                  Didn't even think of that. Sure works well.

                   

                  Always learn something in this forum!

                  Thanks RP

                  vr,

                  Sudhakar