Increase a sequence to a specific value
                                                 Last update (2008-05-15 14:01:28)
                                                                                                                   Date added (2008-05-15 13:50:56)

Summary
Imagine the case where you have a sequence which currently has a value of 500 and for some reason you want to have a value of lets say 20000 (this reason might be after data migrations).

One solution is to drop the sequence and create a new one with initial value the 20000. But this will get invalid all the depended procedures and packages.

The other recommended solution is to run the following pl/sql
-- Paramters:
--   1: sequence name
--   2: table name
--   3: attribute name
--
-- Sample usage:
--   @incseq.sql my_sequence my_table my_attribute
--
--------------------------------------------------------------------------------
--
SET SERVEROUTPUT ON SIZE 1000000;
--
DECLARE
  dummy NUMBER := 0;
  curr  NUMBER := 0;
BEGIN
        --
  SELECT &1..nextval INTO dummy FROM dual;
  dbms_output.put('start with next value=' || dummy);
        --
  SELECT max(&3) INTO curr FROM &2;
  WHILE dummy < curr LOOP
    SELECT &1..nextval INTO dummy FROM dual;
  END LOOP;
  --
  dbms_output.put_line(', end=' || dummy);
  --
END;
/
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search