Oracle 9 - Resetting Sequence to match the state of the table

Question

I have a sequence used to seed my (Integer based) primary keys in an oracle table.

It appears this sequence has not always been used to insert new values into the table. How do I get the sequence back in step with the actual values in the table?

Solanaceous

Answer 1

If ID is the name of your PK column and PK_SEQ is the name of your sequence:

1. Find the value of the highest PK by SELECT MAX(ID) FROM tableName

2. Find the value of the next PK_SEQ by SELECT PK_SEQ.NEXTVAL FROM DUAL

3. If #2 > #1 then nothing needs to be done, assuming you treat these values as true surrogate keys
4. Otherwise, alter the sequence to jump to the max ID by ALTER SEQUENCE PK_SEQ INCREMENT BY [#1 value - #2 value]

5. Bump the sequence by SELECT PK_SEQ.NEXTVAL FROM DUAL

6. Reset the sequence increment value to 1 by ALTER SEQUENCE PK_SEQ INCREMENT BY 1

This all assumes that you don't have new inserts into the table while you're doing this...

Bergeron

I'm struggling with how to do step 3, I've tried various different syntaxes but cant get it to work – Solanaceous

The intent of step 3 is just to compare the largest PK value to the next sequence value. So, for example, if the SELECT from step 1 resulted in a result of 100, and the SELECT from step 2 resulted in a result of 90 that means that you have "jump" 11 sequences. When you alter the sequence in step 4, the SELECT in step 5 will move the sequence 10 values, to 100. After the increment is reset in step 6, the next "SELECT PK_SEQ.NEXTVAL FROM DUAL" will give you 101. – Bergeron

In step 4 I can't get the syntax to do a subtraction in the Increment by clause. I've tried [a-b] a-b and selecr a-b from dual, but not with any success. – Solanaceous

Sorry - it looks like I wasn't clear - you have to determine the value (call it N) and then use the statement "ALTER SEQUENCE PK_SEQ INCREMENT BY N" - I was using the syntax I provided as sort of pseudo code. – Bergeron

I had tried that approach but the problem was a syntaticall issue whereby I was foolishly not appending my variable proparly e.g. EXECUTE IMMEDIATE 'ALTER SEQUENCE NEXT_VALIDATED_TABLE_ID INCREMENT BY ' || N ; – Solanaceous

Answer 2

In short, game it:

-- Current sequence value is 1000

ALTER SEQUENCE x INCREMENT BY -999;
Sequence altered.

SELECT X.NEXTVAL FROM DUAL;
1

ALTER SEQUENCE x INCREMENT BY 1;
Sequence altered.

You can get the max sequence value used within your table, do the math, and update the sequence accordingly.

Bonney

How is this helping. If the max id in the table is 624. How is the above going to correctly set the seq? – Horrific

Answer 3

Declare
  difference INTEGER;
  sqlstmt varchar2(255);
  sequenceValue Number;
begin
sqlstmt := 'ALTER SEQUENCE YOURSEQUENCE INCREMENT BY ';
select YOURSEQUENCE.NEXTVAL into sequenceValue from dual;
select  (nvl(Max(YOURID),0) - sequenceValue)+1 into difference from YOURTABLE;
if difference > 0 then
  EXECUTE IMMEDIATE sqlstmt || difference;
  select  YOURSEQUENCE.NEXTVAL INTO sequenceValue from dual;
  EXECUTE IMMEDIATE sqlstmt || 1;
end if;
end;

Tropology

I think that is because of select YOURSEQUENCE.NEXTVAL into sequenceValue from dual; at line 7, you can check an alternative for it at my answer at https://mcmap.net/q/614264/-oracle-9-resetting-sequence-to-match-the-state-of-the-table, that fetches all_sequences.last_number WHERE sequence_name = 'YOURSEQUENCE' instead of YOURSEQUENCE.NEXTVAL, probably getting the desired value given sequence value caching being disabled. – Centaury

Answer 4

I made this script as I did not find a script online that dynamically sets all my sequences to the current highest ID. Tested on Oracle 11.2.0.4.

DECLARE
  difference         INTEGER;
  sqlstmt            VARCHAR2(255) ;
  sqlstmt2           VARCHAR2(255) ;
  sqlstmt3           VARCHAR2(255) ;
  sequenceValue      NUMBER;
  sequencename       VARCHAR2(30) ;
  sequencelastnumber INTEGER;
  CURSOR allseq
  IS
     SELECT sequence_name, last_number FROM user_sequences ORDER BY sequence_name;
BEGIN
  DBMS_OUTPUT.enable(32000) ;
  OPEN allseq;
  LOOP
    FETCH allseq INTO sequencename, sequencelastnumber;
    EXIT
  WHEN allseq%NOTFOUND;
    sqlstmt  := 'ALTER SEQUENCE ' || sequencename || ' INCREMENT BY ';
    --Assuming: <tablename>_id is <sequencename>
    sqlstmt2 := 'select (nvl(Max(ID),0) - :1)+1 from ' || SUBSTR(sequencename, 1, LENGTH(sequencename) - 3) ;
    --DBMS_OUTPUT.PUT_LINE(sqlstmt2);
    --Attention: makes use of user_sequences.last_number --> possible cache problems!
    EXECUTE IMMEDIATE sqlstmt2 INTO difference USING sequencelastnumber;
    IF difference > 0 THEN
      DBMS_OUTPUT.PUT_LINE('EXECUTE IMMEDIATE ' || sqlstmt || difference) ;
      EXECUTE IMMEDIATE sqlstmt || difference;
      sqlstmt3 := 'SELECT ' || sequencename ||'.NEXTVAL from dual';
      DBMS_OUTPUT.PUT_LINE('EXECUTE IMMEDIATE ' || sqlstmt3 || ' INTO sequenceValue') ;
      EXECUTE IMMEDIATE sqlstmt3 INTO sequenceValue;
      DBMS_OUTPUT.PUT_LINE('EXECUTE IMMEDIATE ' || sqlstmt || 1) ;
      EXECUTE IMMEDIATE sqlstmt || 1;
      DBMS_OUTPUT.PUT_LINE('') ;
    END IF;
  END LOOP;
  CLOSE allseq;
END;

Pashalik

Bit off topic, but for how this can be done in Postgres, see here: cybertec-postgresql.com/en/… – Pashalik

Answer 5

In some cases, you may find it easier to simply get the current max value and then

drop sequence x;
create sequence x start with {current max + 1};

The app will be broken after you do the drop. But that will keep anybody from inserting rows during that period, and creating a sequence is quick. Make sure you recreate any grants on the sequence since those will be dropped when the sequence is. And you may want to manually recompile any plsql that depends on the sequence.

Sansbury

@Jim - I would avoid dropping any db object if there is an alternative. Dropping the sequence will not necessarily prevent inserts on the table that do not reference the sequence. As you point out you also have the extra work of capturing the grants and recompiling dependent objects. – Bergeron

Answer 6

Adding up to https://mcmap.net/q/614264/-oracle-9-resetting-sequence-to-match-the-state-of-the-table, but without resorting to SEQUENCENAME.NEXTVAL hence not resulting in one position over it should be:

DECLARE
  difference INTEGER;
  alter_sequence_statement VARCHAR2 (255);
  sequence_value NUMBER;
BEGIN
  --   Base for the statement that will set the sequence value.
  alter_sequence_statement :=
      'ALTER SEQUENCE SEQUENCENAME INCREMENT BY ';

  --   Fetch current last sequence value used.
  SELECT
    --   You could maybe want to make some further computations just
    -- below if the sequence is using caching.
    last_number
  INTO sequence_value
  FROM all_sequences
  WHERE sequence_owner = 'SEQUENCEOWNER' AND sequence_name = 'SEQUENCENAME';

  --   Compute the difference.
  SELECT max(id) - sequence_value + 1 INTO difference
  FROM SCHEMANAME.TABLENAME;

  IF difference <> 0 THEN
    --   Set the increment to a big offset that puts the sequence near
    -- its proper value.
    EXECUTE IMMEDIATE alter_sequence_statement || difference;

    --   This 'sequence_value' will be ignored, on purpose.
    SELECT SEQUENCENAME.NEXTVAL INTO sequence_value FROM dual;

    --   Resume the normal pace of incrementing one by one.
    EXECUTE IMMEDIATE alter_sequence_statement || 1;
  END IF;
END;

Disclaimer: if the sequence is using caching (all_sequences.cache_size set to bigger than 0) you are probably wanting to take it into consideration in the Compute the difference step.

Oracle documentation for all sequences....

Centaury

Oh well, am sorry this is for Oracle 11, I don't know if it can work in Oracle 9 without changes. – Centaury

Answer 7

• select max value from table & set sequence value to it.

SELECT setval( 'table_id_seq_name', (SELECT MAX(id) FROM table_name ) );

Weirick