Reset every Oracle sequence to the MAX value of its target table PK?

Question

I finally got around to migrating from MySQL to Oracle and was pleasantly surprised just how well of a job the SQLDeveloper Migration tool did. It even took my AUTOINCREMENT fields and created sequences for my tables in their place. It also did a good job of migrating the data as well.

The only real loose end I see is that when it created the sequences, it did not take into account the existing data in the table and instead all of the sequences are starting NEXTVAL of 1.

I can imagine a simple PL/SQL script that will set the next value of the sequence based on the MAX value in the primary key column of a specific table to a specific sequence but then I have to do this over a hundred times and well I just don't have the patience for that.

I wonder if there is some way I can write a PL/SQL script that uses meta data in the SYSTEM schema to dynamically do this for every table/sequence pair in an entire user space? Does anybody have any other better ideas how to handle this? I ran out of interns BTW.

Answer 1

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;

Answer 2

After years of being away from PL/SQL I was able to muster the following procedure that did a good job of fixing most of my sequences. The hook is that apparently after running the MySQL to Oracle Migration tool of SQL Developer when logged in as SYSTEM, it apparently creates a series of meta data tables on the SYSTEM user space.

MD_TABLES ... MD_SEQUENCES, etc...

While the tool did not set the sequences, it turns out that the MD_SEQUENCES table contains a column SEQ_START that already has what NEXTVAL should return for the following sequence. I used this information in the following procedure to do it for all tables in the FFL user space.

set serveroutput on;
/

DECLARE
TYPE name_array is array(50) OF varchar2(100);
var_tables  name_array; 
var_seq_id number(20);

cursor c1 is
SELECT distinct table_name
  FROM system.md_tables;

cursor c2 is
SELECT distinct name, seq_start from system.md_sequences;

BEGIN

  FOR i IN c2
    LOOP
          select regexp_substr(i.name, '[^_]+', 1, level) as output bulk collect into var_tables
          from dual
          connect by level <= length(regexp_replace(i.name, '[^_]+')) + 1;
        BEGIN
         execute immediate 'select max(' || var_tables(2) || ') from ffl.' || var_tables(1) || ' ' into var_seq_id;
         dbms_output.put_line(var_seq_id);

          execute immediate
          'alter sequence ffl.' || i.name || ' increment by ' || var_seq_id || ' minvalue 0';

          execute immediate
          'select ffl.' || i.name || '.nextval from dual' INTO var_seq_id;

          execute immediate
          'alter sequence ffl.' || i.name || ' increment by 1 minvalue 0';

        EXCEPTION WHEN OTHERS THEN
         dbms_output.put_line('Exception at column id: ' || var_tables(2));
        END;
    END LOOP;
END;