what is the tables missing on MySQL/MariaDB from Oracle DB content.

if Oracle SQL developer can connect to both DB, can I use simple SQL statement to compare both side?

I am not a programmer and I don't think I can do this :

https://stackoverflow.com/questions/6260846/during-migration-from-oracle-to-mysql-how-to-compare-data-between-two-using-java

and anyone know if this one:

https://docs.oracle.com/cd/E17952_01/mysql-utilities-1.5-en/mysqldbcompare.html

is to compare between 2 x diff database on schema?


how about I extract the tablename only to an excel and compare it in excel ? excel can compare 2 x csv ?

If you cannot find a good comparison tool, one approach is to use EXCEPT operator.

In essence, select table names from MySQL information_schema:

SELECT TABLE_NAME
FROM  INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = 'your_mysql_db'

Select allOpen in new window

Dump the results into a temp table on the Oracle DB and use EXCEPT:

SELECT table_name FROM all_tables
EXCEPT 
SELECT table_name FROM your_mysql_table_names

Select allOpen in new window

If you flip the order of the queries, you will get tables on the MySQL side that do not show up in Oracle.

Hope that helps.

"Dump the results into a temp table on the Oracle DB and use EXCEPT:

hi good direction, I am thinking about can I dump the result from mySQL to Oracle in an easy way, we have a lot of record on MySQL and Oracle side, what is the way you use ?

Do using diff tools and export result from both side to the respective CSV, then only compare the CSV diff is easier ?

also what oracle query list down all tables names owned by a schema ?

SELECT table_name FROM all_tables

done, I added my schema name

then how to import that list to oracle from mysql  ? I am using SQL developer and am not sure if I can use it to import that result set to oracle, any idea?

or what is the query to insert data from that result set to oracle if I connect to MySQL/MariaDB from SQL developer.

in my sql side, I can also do this :

SELECT TABLE_NAME
FROM  INFORMATION_SCHEMA.tables
WHERE TABLE_SCHEMA = '<my schema>'

what is the different between INFORMATION_SCHEMA.tables and INFORMATION_SCHEMA.PARTITIONS ?

I just implement that but sorry, result is wrong ! the first table from the EXCEPT result set already exists in BOTH DB.

one thing man, how about I move the MySQL result set to ANOTHER MySQL and compare based on that MySQL ? that MySLQ supposed to have a full table set.

Hi,

>what is the different between INFORMATION_SCHEMA.tables and INFORMATION_SCHEMA.PARTITIONS ?
List of all non-tempary tables and views
https://mariadb.com/kb/en/library/information-schema-tables-table/

Lists  all partitions of non-tempary partitioned tables as well as all non-tempary regular tables (with partition info as nulls)
https://mariadb.com/kb/en/library/information-schema-partitions-table/

Regards,
     Tomas Helgi

tks man,  but if EXCEPT operator is not working how can I compare data set  and find out what is missing in MySQL/MariaDB ?

MySQL doesn’t have EXCEPT operator which is why I said to move to Oracle. On incorrect results, it could be case sensitivity so may change all the results to upper or lower case before comparison.

If both schemas on the same platform, the dbcompare should work just fine.  The EXCEPT is meant for use if you cannot use the dB compare tools.

Kevin Cross,

"MySQL doesn’t have EXCEPT operator which is why I said to move to Oracle."

I type that command in oracle as i clearly understand what you mean

" On incorrect results, it could be case sensitivity so may change all the results "

I am sorry that I just export and import the SAME table name list, I can't see why it is a problem.

" the dbcompare should work just fine"

show me the URL to download please.

now I just use select count(*) and compare as all table name is different.

Don’t know what you mean by count(*) here.  You are comparing the names, correct?  If your list of string names is in uppercase in one table and lowercase in the other, then convert one to match the other like to uppercase then your comparison will be apples to apples.  If you are comparing characters 'A' not equal 'a' and so "ABC", "abc" and "AbC" are different strings in a case sensitive database.

You posted the link to DB compare up top, so my post was referring to what you posted.

"Don’t know what you mean by count(*) here.  "

I mean easy way to check if table are missing on either side can count(*) on both side, if they are the same then number will be the same, this can be the first step.

"You are comparing the names, correct?

and also NUMBER of table on both side. e.g. is both side same number of table AFTER conversion?

after i convert to MySQL and the TABLE NAME must be all the SAME before and after.

"You posted the link to DB compare up top, so my post was referring to what you posted."

basically you are right, but my first statement in my question is:
"Now I have convert Oracle to MariaDB/MySQL and we have a convern, how can we know the number of table is the same! "

"then convert one to match the other like to uppercase then your comparison will be apples to apples."

yes, this is what I am doing. DB migration from Oracle to MySQL.

EXCEPT doesn't work.