SQL Developer MySQL to Oracle migration auto_increment and identity columns

Question

Ok, So in learning about migrating from MySQL 8 to Oracle 19 I have run into an issue. My tables in MySQL have auto_increment on the primary key. When I walk through the migration and get my master.sql script that has all of my metadata for object creation. I have noticed that the DDL for the tables has the identity column starting at 0 and not the auto_increment number from MySQL. This runs into an issue when inserting data if the number of rows in the table in Oracle is less than the identity column base value. I used Goldengate to insert the data and the sequence that is system generated for the identity column only gets bumped up for each insert of data. How can I get the correct value when using SQL Developer to do a migration? I have a work around in PL/SQL but would like to get the correct value form the migration process. Thanks

CREATE TABLE my_table (
 id NUMBER(10,0) GENERATED BY DEFAULT ON NULL AS IDENTITY START WITH 0 INCREMENT BY 1 MINVALUE 0 NOMAXVALUE ,
 created DATE NOT NULL,
 modified DATE NOT NULL,
 user_id NUMBER(10,0) NOT NULL
);

should be

CREATE TABLE my_table (
 id NUMBER(10,0) GENERATED BY DEFAULT ON NULL AS IDENTITY START WITH 100 INCREMENT BY 1 MINVALUE 0 NOMAXVALUE ,
 created DATE NOT NULL,
 modified DATE NOT NULL,
 user_id NUMBER(10,0) NOT NULL
);

• Barmar: The starting value generally shouldn't matter. All that matters for columns like this is that they're unique.
• Barmar: It's not clear what kind of answer you expect here. If you consider this wrong, you should submit a bug report about the migration tool.
• nbk: it does eaxcly what you wanted dbfiddle.uk/s57SKrXX and starts with 100
• cptkirkh: I will put in a bug since if you see my comments below there will be an issue.

Answer 1

A better option might be to add an additional step.

Step 1: Create the table

CREATE TABLE my_table (
 id NUMBER(10,0) GENERATED BY DEFAULT ON NULL AS IDENTITY START WITH 0 INCREMENT BY 1 MINVALUE 0 NOMAXVALUE ,
 created DATE NOT NULL,
 modified DATE NOT NULL,
 user_id NUMBER(10,0) NOT NULL
);

Step 2: insert the migrated data with primary key values.

INSERT INTO my_table VALUEs (100,SYSDATE, SYSDATE,1);
INSERT INTO my_table VALUEs (101,SYSDATE, SYSDATE,2);
INSERT INTO my_table VALUEs (110,SYSDATE, SYSDATE,3);

Step 3: reset the identity column based on current highest value.

ALTER TABLE my_table MODIFY ID GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH LIMIT VALUE);

The next row will now generate id value 111. Doing it this way you don't need to bother calculating the initial value.

• nbk: no need for that see my comment
• Koen Lostrie: In that case I don't understand what your actual question is...
• cptkirkh: @Koen Lostrie that is what I had to do an alter table after the fact. I don't understand why SQL Developer doesn't pull the auto_increment value when it generates the DDL for the table. You can see that DDL when you use MySQL workbench and pull the MySQL DDL.
• cptkirkh: You see this becomes in an issue if the number of rows is less than the auto_increment value in MYSQL. Then the inserts that happen in Goldengate only go up to the number of rows and then identity sequence is then behind the max number for the column.
• Koen Lostrie: A table with an identity column is a table with an index behind the scenes. The DDL for a table and sequence is generated from the data dictionary, I assume dbms_metadata.get_ddl. The data dictionary does not store the last value of the sequence - that part is up to you.