DB utilities - updating sequences the elegant way Oracle Headquarters Redwood Shores1 e1698667100526

DB utilities – updating sequences the elegant way

Ever needed to update a sequence so the next value is greater than the maximum value of a table? Well, the quick and dirty ways are

  1. select SEQ.nextval from dual;

    (ever did this a million times?)

  2. drop sequence SEQ;
    create sequence SEQ start with MAX+1;
    

    (never had problems with grants and invalided stored procedures?)

If you’re interested in a more elegant and robust way…

The SQL*Plus script upd_seq.sql allows you to specify a sequence, table and column. The script updates the sequence so that the next value is the smallest value larger than the current maximum of the table column (unless the current sequence value is already at least the maximum table column value).

See the documentation in the script for more information.

2 Comments

  1. Marco May 23, 2005
  2. Marcos May 23, 2005