SQL to bump up a sequence's Last Value

[urge262]

I have a sequence on a table and I need to bump it up.

Sequence Name: SQ_SRVC_CT

the Last Value: 43

I need the last value changed to 1155 so that the next record will have an ID of 1156.

I tried: Alter sequence SQ_SRVC_CT next value 1155

but I get the error: ORA-02286: no options specified for ALTER SEQUENCE

Also tried: alter sequence SQ_SRVC_CT lastvalue 1155 ORA-02286: no options specified for ALTER SEQUENCE

 

[carp]

Please just post on the appropriate forum.
As I mentioned on the Oracle 5/6/7 forum, you'll need to drop the sequence and recreate it using "START WITH 1155".

 

[rtefft]

You can "fast-forward" the sequence using a single SQL loop if you need to. Dropping and recreating the sequence means rebuilding grants and synonyms, and possible recompiling PL/SQL as well. When we use a sequence to generate a sequential number for a table's primary key, we include this logic to "catch up" the sequence if the table's PK has gotten ahead of it (can happen if the table is restored from backup or imported from another DB). The code below is used to generate the next CONTROL_NO for the WORK_IN_PROGRESS table:

function NEXT_CONTROL_NO return number is
  v_count     number := 1;
  v_ctrl_no   WORK_IN_PROGRESS.control_number%type;
begin
  while v_count > 0 loop
    select WORKINPROGRESS_CTRL_SEQ.NEXTVAL
    into v_ctrl_no from dual;
    --
    --  If the ctrl# exists, the sequence is out of sych (probably from an import).
    --  V_COUNT will be > 1, so the loop will cycle and try again.  This will
    --  continue until the sequence catches up to the table.
    --
    select count(*) into v_count
    from WORK_IN_PROGRESS
    where control_number = v_ctrl_no;
  end loop;
  --
  return v_ctrl_no;
end NEXT_CONTROL_NO;

Rich

____________________________
Rich Tefft
PL/SQL Programmer

 

[urge262]

Thanks Rich,

I am going try this out on monday. Once I run some insert scripts.