ALTERING AND DROPPING THE SEQUENCE

We can alter the sequence to perform the following.

• Set or eliminate minvalue or maxvalue.
• Change the increment value.
• Change the number of cached sequence numbers

Guide line for altering the sequence or modifying the sequence

• We must be the owner of the sequence or should have privileges to ALTER the sequence.
• Only future sequence number is affected by ALTER SEQUENCE statement.
• The START WITH option can not be changed using ALTER SEQUENCE. The sequence must be dropped and re-created in order to restart the sequence at different number.

Syntax:-

SQL>ALTER SEQUENCE 

[INCREMENT BY ]

[{MINVALUE }]

[{MAXVALUE }]

[{CYCLE/NOCYCLE}]

[{CACHE /NOCACHE}];

Example:-

SQL>ALTER SEQUENCE sampleseq

          MAXVAL 10

          CACHE

          NOCYCLE;

Viewing the Current value of sequence

SQL>select .CURRVAL from dual;

Example:-

SQL>select sampleseq.CURRVAL from dual;

-- Output

CURRVAL

   --------

       4

DROPPING THE SEQUENCE:-

To remove the sequence from data dictionary , use the DROP SEQUENCE statement. We must be the owner of the sequence or have the DROP ANY SEQUENCE privilege to remove it.

Syntax:-

SQL>Drop sequence ;

Example:-

SQL>drop sequence ;

--sequence dropped