When using SQL Developer to migrate from MySQL 8 to Oracle 19 I have run into an issue when a primary key has to auto_increment feature. When I run the migration the ddl for the table that gets created and the identity column starts on 0 instead of what the auto_increment is set for in MySQL. That is an issue.
This is what gets created by SQL Developer. How do I get SQL Developer to get the right number? The issue is if the ID column has a higher number for ID than the number of rows in the table when we go to insert after the data is migrated the identity column the identity column has a vlaue of the number of rows in 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
);
--This is what I need
CREATE TABLE my_table (
id NUMBER(10,0) GENERATED BY DEFAULT ON NULL AS IDENTITY START WITH 120 INCREMENT BY 1 MINVALUE 0 NOMAXVALUE ,
created DATE NOT NULL,
modified DATE NOT NULL,
user_id NUMBER(10,0) NOT NULL
);