Altering Oracle sequences Oracle Database Tips by Donald Burleson

In Oracle it is possible to alter an existing Oracle sequence. To accomplish this you can use the Oracle ALTER SEQUENCE command. I recommend that before executing the Oracle ALTER SEQUENCE command,  sequence caching should be turned off to avoid problems:

    ALTER SEQUENCE seq_cache NOCACHE;.
   ALTER SEQUENCE seq_cache INCREMENT BY xx

In RAC, sequence enqueue delays are shown in the eq_type column of the gv$enqueue_stat view. A value of "SQ Enqueue" indicates that there is contention for sequences. In almost all cases, executing an Oracle  ALTER SEQUENCE command can increase the cache size of sequences used by the application.

When creating sequences for a RAC environment, DBAs should use the NOORDER keyword to avoid an additional cause of SQ enqueue contention that is forced ordering of queued sequence values.

The Oracle Documentation gives us the following ALTER SEQUENCE Command Figure:

The Oracle Documentation gives us the description of the ALTER SEQUENCE Command listed above:

"The ALTER SEQUENCE command syntax contains the words ALTER SEQUENCE followed by:

1. Schema name, which, if used, should be separated from the sequence name by a period.

2. Sequence name, which can be defined with or without the schema name before it.

3. And optionally can use one or more of the following:

   • INCREMENT BY followed by the increment number.

   • MAXVALUE followed by an integer

   • NO MAXVALUE

   • MINVALUE followed by an integer

   • NOMINVALUE

4. End statement with a semicolon."

See my other notes on Oracle sequences here:

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.