Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

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;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.