Migration MySQL to Oracle 19C error ORA-00942

Question

I'm trying to migrate a simple MySQL test database to Oracle 19c using the migration wizard available in Oracle SQL Developer, the result is that the procedure create the user and the tables but the tables are empty and i receive the following error "ORA-00942 table or view does not exist".

enter image description here enter image description here

<?xml version="1.0" encoding="windows-1252" standalone="no"?>
<log>
<record>
 <date>2023年01月25日T08:52:10.649730400Z</date>
 <nanos>730400</nanos>
 <logger>oracle.dbtools.migration.workbench.core.MigrationLogResourceBundle</logger>
 <level>INFO</level>
 <class>oracle.dbtools.migration.convert.ConverterWorker</class>
 <message>Eseguito il coalesce del catalogo MySQL, schema test nello schema singolo test</message>
 <key>Convert.COALESCED</key>
 <catalog>oracle.dbtools.migration.workbench.core.MigrationLogResourceBundle</catalog>
 <param>MySQL</param>
 <param>test</param>
 <param>test</param>
 <param>oracle.dbtools.migration.workbench.core.logging.LogInfo@61735376</param>
</record>
<record>
 <date>2023年01月25日T08:52:11.575035900Z</date>
 <nanos>35900</nanos>
 <logger>oracle.dbtools.migration.workbench.core.MigrationLogResourceBundle</logger>
 <level>INFO</level>
 <class>oracle.dbtools.migration.core.util.MultiLevelLogManager</class>
 <message>Colonna 'number' modificato in 'number_' (Caratteri non validi, parola riservata o conflitto)</message>
 <param>oracle.dbtools.migration.workbench.core.logging.LogInfo@61735376</param>
</record>
<record>
 <date>2023年01月25日T08:52:14.770235800Z</date>
 <nanos>235800</nanos>
 <logger>oracle.dbtools.migration.workbench.core.MigrationLogResourceBundle</logger>
 <level>INFO</level>
 <class>oracle.dbtools.migration.workbench.core.ui.FullMigrateTask</class>
 <message>Conversione completata in 0 secondi</message>
 <param>oracle.dbtools.migration.workbench.core.logging.LogInfo@61735376</param>
</record>
<record>
 <date>2023年01月25日T08:52:20.872501Z</date>
 <nanos>501000</nanos>
 <logger>oracle.dbtools.migration.workbench.core.MigrationLogResourceBundle</logger>
 <level>SEVERE</level>
 <class>oracle.dbtools.migration.workbench.core.logging.MigrationLogUtil</class>
 <message>Generation phase
ORA-00942: tabella o vista inesistente
</message>
 <param>oracle.dbtools.metadata.persistence.PersistableObject.doUpdate(PersistableObject.java:364)</param>
 <param>oracle.dbtools.metadata.persistence.PersistableObject.save(PersistableObject.java:186)</param>
 <param>oracle.dbtools.migration.workbench.core.ui.FullMigrateTask.doGenerate(FullMigrateTask.java:1303)</param>
 <param>oracle.dbtools.migration.workbench.core.ui.FullMigrateTask.doMaskBasedActions(FullMigrateTask.java:437)</param>
 <param>oracle.dbtools.migration.workbench.core.ui.FullMigrateTask.doWork(FullMigrateTask.java:321)</param>
 <param>oracle.dbtools.migration.workbench.core.ui.FullMigrateTask.doWork(FullMigrateTask.java:154)</param>
 <param>oracle.dbtools.raptor.backgroundTask.RaptorTask.call(RaptorTask.java:199)</param>
 <param>java.base/java.util.concurrent.FutureTask.run(Unknown Source)</param>
 <param>oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$RaptorFutureTask.run(RaptorTaskManager.java:702)</param>
 <param>java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)</param>
 <param>java.base/java.util.concurrent.FutureTask.run(Unknown Source)</param>
 <param>java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)</param>
 <param>java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)</param>
 <param>java.base/java.lang.Thread.run(Unknown Source)</param>
 <param>oracle.dbtools.migration.workbench.core.logging.LogInfo@15963e4b</param>
 <exception>
 <message>oracle.dbtools.migration.workbench.core.ui.FullMigrateTask$FullMigrateTaskException: Generation phase
ORA-00942: tabella o vista inesistente
</message>
 <frame>
 <class>oracle.dbtools.metadata.persistence.PersistableObject</class>
 <line>364</line>
 </frame>
 <frame>
 <class>oracle.dbtools.metadata.persistence.PersistableObject</class>
 <line>186</line>
 </frame>
 <frame>
 <class>oracle.dbtools.migration.workbench.core.ui.FullMigrateTask</class>
 <line>1303</line>
 </frame>
 <frame>
 <class>oracle.dbtools.migration.workbench.core.ui.FullMigrateTask</class>
 <line>437</line>
 </frame>
 <frame>
 <class>oracle.dbtools.migration.workbench.core.ui.FullMigrateTask</class>
 <line>321</line>
 </frame>
 <frame>
 <class>oracle.dbtools.migration.workbench.core.ui.FullMigrateTask</class>
 <line>154</line>
 </frame>
 <frame>
 <class>oracle.dbtools.raptor.backgroundTask.RaptorTask</class>
 <line>199</line>
 </frame>
 <frame>
 <class>java.util.concurrent.FutureTask</class>
 </frame>
 <frame>
 <class>oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$RaptorFutureTask</class>
 <line>702</line>
 </frame>
 <frame>
 <class>java.util.concurrent.Executors$RunnableAdapter</class>
 </frame>
 <frame>
 <class>java.util.concurrent.FutureTask</class>
 </frame>
 <frame>
 <class>java.util.concurrent.ThreadPoolExecutor</class>
 </frame>
 <frame>
 <class>java.util.concurrent.ThreadPoolExecutor$Worker</class>
 </frame>
 <frame>
 <class>java.lang.Thread</class>
 </frame>
 </exception>
</record>
<record>
 <date>2023年01月25日T08:52:20.877506400Z</date>
 <nanos>506400</nanos>
 <logger>oracle.dbtools.migration.workbench.core.MigrationLogResourceBundle</logger>
 <level>WARNING</level>
 <class>oracle.dbtools.migration.workbench.core.ui.FullMigrateTask</class>
 <message>L'esecuzione dello script generato include errori: FAILED : Migrazione del database : FAILED</message>
 <param>oracle.dbtools.migration.workbench.core.logging.LogInfo@2b459c32</param>
</record>

MySQL queries:

CREATE DATABASE `test` /*!40100 COLLATE 'utf8_general_ci' */;
CREATE TABLE `test` (
 `number` INT(11) NULL DEFAULT NULL,
 `string` VARCHAR(50) NULL DEFAULT NULL
)
ENGINE=InnoDB;
INSERT INTO `test`.`test` (`number`, `string`) VALUES ('1', 'test');
INSERT INTO `test`.`test` (`number`, `string`) VALUES ('2', 'test');

This is the master.sql generated from the wizard:

SET ECHO ON
SET VERIFY ON
SET FEEDBACK ON
SET DEFINE ON
CLEAR SCREEN
set serveroutput on
COLUMN date_time NEW_VAL filename noprint;
SELECT to_char(systimestamp,'yyyy-mm-dd_hh24-mi-ssxff') date_time FROM DUAL;
spool "Test5_&filename..log"
-- Password file execution
@passworddefinition.sql
PROMPT Dropping Role ROLE_Test5 ...
DROP ROLE ROLE_Test5 ;
PROMPT Creating Role ROLE_Test5 ...
CREATE ROLE ROLE_Test5 ;
-- PROMPT Drop test user
-- drop user test cascade;
 
PROMPT Create user test
CREATE USER test IDENTIFIED BY &&test_password PASSWORD EXPIRE ACCOUNT LOCK /* DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP */;
GRANT CREATE SESSION, RESOURCE, CREATE VIEW, CREATE MATERIALIZED VIEW, CREATE SYNONYM, UNLIMITED TABLESPACE TO test;
-- PROMPT Drop Emulation user
-- drop user Emulation cascade;
 
PROMPT Create user Emulation
CREATE USER Emulation IDENTIFIED BY &&Emulation_password PASSWORD EXPIRE ACCOUNT LOCK /* DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP */;
GRANT CREATE SESSION, RESOURCE, CREATE VIEW, CREATE MATERIALIZED VIEW, CREATE SYNONYM, UNLIMITED TABLESPACE TO Emulation;
set define on
prompt connecting to Emulation
alter session set current_schema=Emulation;
set define off
set define on
prompt connecting to test
alter session set current_schema=test;
set define off
-- DROP TABLE test CASCADE CONSTRAINTS;
PROMPT Creating Table test ...
CREATE TABLE test (
 number_ NUMBER(10,0),
 string VARCHAR2(50 CHAR)
);
GRANT ALL ON test TO ROLE_Test5;
set define on
prompt connecting to test
alter session set current_schema=test;
set define off
spool off;
COMMIT;

I've also tryied to create the user and grant unlimited quota on all the table_spaces but the result is the same. Anyone have an idea of what could be the problem?

• pmdba: We need to see the actual SQL involved to understand what's going on, and any other errors generated from the DDL commands. Please provide examples.
• Tinfo: @pmdba i've added more informations, please tell me if they are enough or you need more, thanks.
• pmdba: Your question said you were migrating into Oracle 19c; that's where the error is coming from. Please include the DDL and SQL insert commands and their output for Oracle 19c. We need to see the code that is causing the error(s).
• Tinfo: @pmdba i've added the master.sql generated from the SQL Developer wizard and after the table create query i don't see any insert command so maybe is the wizard doesn't create the correct SQL file, i can't find any DDL in the same folder.
• pmdba: It looks like the table should be created correctly (you didn't include any output, but the privileges all look good), so it would come down to how the inserts are being executed. What user is running the inserts? if not running as the test user, is the session configured correctly and does the executing user have the right privileges to do the insert? Your ORA-00942 error would suggest not.

Answer 1

Ensure that your oracle user has the following privileges: resource, create table, select and insert. Also verify that the table actually exists in Oracle using a privileged user:

select owner, table_name 
from all_tables 
where upper(table_name) = 'TEST';