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

marrowyung
marrowyung used Ask the Experts™
on
hi,

Now I have convert Oracle to MariaDB/MySQL and we have a convern, how can we know the number of table is the same! but the main idea should be what tables is missed from MariaDB/MySQL side?

any easy way to compare what table is missed on MySQL/mariaDB side ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
marrowyungSenior Technical architecture (Data)

Author

Commented:
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 ?
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
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'

Open 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

Open 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.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"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 ?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

marrowyungSenior Technical architecture (Data)

Author

Commented:
also what oracle query list down all tables names owned by a schema ?
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
SELECT table_name FROM all_tables
marrowyungSenior Technical architecture (Data)

Author

Commented:
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.
marrowyungSenior Technical architecture (Data)

Author

Commented:
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.
marrowyungSenior Technical architecture (Data)

Author

Commented:
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.
Tomas Helgi JohannssonDatabase Administrator / Software Engineer

Commented:
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
marrowyungSenior Technical architecture (Data)

Author

Commented:
tks man,  but if EXCEPT operator is not working how can I compare data set  and find out what is missing in MySQL/MariaDB ?
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
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.
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
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.
marrowyungSenior Technical architecture (Data)

Author

Commented:
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.
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
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.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial