0

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-25T08: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-25T08: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-25T08: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-25T08: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-25T08: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?

Improve this question
5
  • 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.
    – pmdba
    Jan 24 at 21:20
  • @pmdba i've added more informations, please tell me if they are enough or you need more, thanks.
    – Tinfo
    Jan 25 at 9:01
  • 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).
    – pmdba
    Jan 25 at 13:12
  • @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.
    – Tinfo
    Jan 27 at 13:21
  • 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.
    – pmdba
    Jan 27 at 15:46

1 Answer 1

Reset to default
0

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';
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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