自动将 Oracle 表与 MySQL 表同步

Automate Syncing Oracle Tables With MySQL Tables

提问by Clint Davis

The university I work at uses Oracle for the database system. We currently have programs we run at night to download what we need into some local Access tables for our testing needs. Access is getting to small for this now and we need something bigger. Also, the nightly jobs require constant maintance to keep working (because of network issues, table changes, bad code :) ) and I would like to eliminate them to free us up for more important things.

我工作的大学使用 Oracle 作为数据库系统。我们目前有我们在晚上运行的程序，用于将我们需要的内容下载到一些本地 Access 表中，以满足我们的测试需求。现在访问变得越来越小，我们需要更大的东西。此外，夜间工作需要不断维护才能继续工作（因为网络问题、表更改、错误代码 :)），我想消除它们，让我们腾出时间去做更重要的事情。

I am most familiar with MySQL so I setup a test MySQL server. What is the best way to automate copying the needed tables from Oracle to MySQL?

我最熟悉 MySQL，所以我设置了一个测试 MySQL 服务器。自动将所需表从 Oracle 复制到 MySQL 的最佳方法是什么？

Edit: I accepted the answer. I don't like the answer but it seems to be correct based on further research and the lack of other answers provided. Thanks to all for pondering my question and answering it.

编辑：我接受了答案。我不喜欢这个答案，但根据进一步的研究和提供的其他答案的缺乏，它似乎是正确的。感谢大家思考并回答我的问题。

采纳答案by Matthew Watson

I don't think there is really anything that is going to do this. If you could setup a local Oracle database, then most likely you could as oracle has various means of keeping two databases "in sync", provided they are both Oracle.

我不认为真的有任何事情可以做到这一点。如果您可以设置本地 Oracle 数据库，那么很可能您可以因为 oracle 有各种方法来保持两个数据库“同步”，前提是它们都是 Oracle。

If you must use mysql, then likely you are going to just have to write something to sync the data, this is of course always going to run in the same problems you currently have with the access "database".

如果您必须使用 mysql，那么您可能只需要编写一些内容来同步数据，这当然总是会遇到与您当前访问“数据库”时遇到的相同问题。

You could setup something with HSODBC and triggers, but

您可以使用 HSODBC 和触发器进行设置，但是

1. I've found HSODBC to be very memory hungry
2. This is only going to add more load to your DB, which you say is already heavily loaded during the day.

1. 我发现 HSODBC 非常需要内存
2. 这只会给你的数据库增加更多的负载，你说它在白天已经很重了。

If the main thing you are doing is wanting a local Test copy of your oracle database, you would be best to setup syncing with a local version of oracle, as far as I can tell from the licenses, oracle is free for development copies ( I have seen some posts to the contrary, but if you find that is the case, you could always use something like Oracle XE)

如果您正在做的主要事情是想要您的 oracle 数据库的本地测试副本，您最好设置与本地版本的 oracle 同步，据我从许可证中得知，oracle 可免费用于开发副本（我看过一些相反的帖子，但如果你发现是这种情况，你总是可以使用像 Oracle XE 这样的东西）

回答by Preston

Could you just copy the Oracle tables and then set them up as linked tables in MS Access? This way the front-end stays the same plus you keep everything in Oracle (less moving parts than exporting and importing).

您可以复制 Oracle 表，然后在 MS Access 中将它们设置为链接表吗？这样，前端保持不变，而且您将所有内容都保留在 Oracle 中（与导出和导入相比，移动部分更少）。

回答by reiniero

As Kellyn said, there are lots of free tools. One of them is SQLWorkbench http://www.sql-workbench.net/, which works with any JDBC database, so MySQL and Oracle should work. It can create tables in Oracle if needed, or just only copy over the (updated) data.

正如凯林所说，有很多免费工具。其中之一是 SQLWorkbench http://www.sql-workbench.net/，它适用于任何 JDBC 数据库，因此 MySQL 和 Oracle 应该可以工作。如果需要，它可以在 Oracle 中创建表，或者只复制（更新的）数据。

回答by Sree Lakshmi

There are many tool available to migrate data from oracle to mysql if your database is not very complicated.

如果你的数据库不是很复杂，有很多工具可以将数据从 oracle 迁移到 mysql。

You can use open source tools like Kettle pentaho ETL tool or paid enterprise tools like DB convert: https://dbconvert.com/oracle/mysql/

您可以使用 Kettle pentaho ETL 工具等开源工具或 DB convert 等付费企业工具：https: //dbconvert.com/oracle/mysql/

Lastly you can write a script or program that migrates the data.

最后，您可以编写迁移数据的脚本或程序。

Please find links related to your question:

请找到与您的问题相关的链接：

https://dba.stackexchange.com/questions/150343/how-to-sync-a-mysql-db-with-a-oracle-db

https://dba.stackexchange.com/questions/150343/how-to-sync-a-mysql-db-with-a-oracle-db

Migrate from Oracle to MySQL

从 Oracle 迁移到 MySQL