Modifying a sequence object

We can alter a sequence object to perform tasks, such as restarting the sequence, changing the sequence behavior like increment interval and other attributes. We cannot change the sequence data type once it is created. In such cases, we need to drop the sequence and recreate with the new definition. If a sequence is altered, then all the values present in cache are also lost.

Getting ready

We need the ALTER privilege on the sequence object to alter a sequence. The creator of the sequence automatically gets USAGE and ALTER privileges on the sequence. The ALTER statement can be embedded in an application program or can be issued as simple SQL.

How to do it...

We can use the ALTER SEQUENCE command to modify a sequence object. Let's see a few examples of sequence modification:

• In the following example, ALTER is a sequence with a new MINVALUE:

ALTER SEQUENCE item_num MINVALUE 1000;

• In the following example, RESTART is a sequence with a numeric value:

ALTER SEQUENCE...