ALTER SEQUENCE Concept and Example

In Oracle, you can alter an existing sequence using the ALTER SEQUENCE statement. This allows you to change various properties of the sequence, such as its increment value, minimum and maximum values, caching behavior, and whether it cycles or not.

Options for Altering a Sequence:

1. INCREMENT BY: Changes the interval between sequence numbers.

   
   ALTER SEQUENCE sequence_name INCREMENT BY new_increment_value;
   

   • Example:

     ALTER SEQUENCE emp_seq INCREMENT BY 5;
     

2. MINVALUE: Sets a new minimum value for the sequence.

   
   ALTER SEQUENCE sequence_name MINVALUE new_min_value;
   

   • Example:

     ALTER SEQUENCE emp_seq MINVALUE 50;

3. MAXVALUE: Sets a new maximum value for the sequence.

   
   ALTER SEQUENCE sequence_name MAXVALUE new_max_value;
   

   • Example:

     ALTER SEQUENCE emp_seq MAXVALUE 10000;

4. CYCLE/NOCYCLE: Specifies whether the sequence should start over when it reaches the maximum value.

   • CYCLE: Allows the sequence to restart from the minimum value after reaching the maximum value.
   • NOCYCLE: Prevents the sequence from restarting after reaching the maximum value.

   
   ALTER SEQUENCE sequence_name CYCLE;
   ALTER SEQUENCE sequence_name NOCYCLE;
   

   • Example:

     sql
     ALTER SEQUENCE emp_seq CYCLE;
     

5. CACHE/NOCACHE: Adjusts how many sequence numbers are preallocated and stored in memory.

   • CACHE: Specifies the number of sequence numbers to cache for faster access.
   • NOCACHE: Disables caching of sequence numbers.

   
   ALTER SEQUENCE sequence_name CACHE cache_size;
   ALTER SEQUENCE sequence_name NOCACHE;
   

   • Example:

     
     ALTER SEQUENCE emp_seq CACHE 50;
     ALTER SEQUENCE emp_seq NOCACHE;
     

6. ORDER/NOORDER: Ensures that sequence numbers are generated in order of request.

   • ORDER: Guarantees that sequence numbers are generated in the order they are requested.
   • NOORDER: Does not guarantee sequence numbers are generated in order.

   
   ALTER SEQUENCE sequence_name ORDER;
   ALTER SEQUENCE sequence_name NOORDER;
   

   • Example:

     
     ALTER SEQUENCE emp_seq NOORDER;
     

7. RESTART: Resets the sequence value.

   
   ALTER SEQUENCE sequence_name RESTART START WITH new_start_value;
   

   • Example:

     
     ALTER SEQUENCE emp_seq RESTART START WITH 200;
     

Important Notes:

• You cannot directly decrease the value of an already generated sequence. However, by using RESTART, you can reset the sequence to a lower value if needed.
• Any changes made using ALTER SEQUENCE only affect future values generated by the sequence. They do not retroactively affect values that have already been generated.

Example Scenario:

Suppose you have a sequence order_seq with the following properties:

CREATE SEQUENCE order_seq
  START WITH 1000
  INCREMENT BY 10
  MAXVALUE 9999
  CYCLE
  CACHE 20;

Now, you want to:

1. Increase the increment to 15.
2. Set the maximum value to 5000.
3. Change it to NOCYCLE, so it doesn't restart after reaching the max value.
4. Disable caching.

You would execute:

ALTER SEQUENCE order_seq INCREMENT BY 15;
ALTER SEQUENCE order_seq MAXVALUE 5000;
ALTER SEQUENCE order_seq NOCYCLE;
ALTER SEQUENCE order_seq NOCACHE;

These commands will adjust the sequence accordingly for future number generations.