从 Oracle 导出到 SQL Server 或 mySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2026816/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
export from Oracle into SQL Server or mySQL
提问by thegunner
Is there a standard or recommended method of exporting the data from an Oracle DB into a SQL Server or mySQL database? Was thinking exporting the Oracle data into XML then importing the XML into SQL Server...or this recommmened?
是否有将数据从 Oracle DB 导出到 SQL Server 或 mySQL 数据库的标准或推荐方法？是考虑将 Oracle 数据导出为 XML，然后将 XML 导入 SQL Server ……还是建议这样做？
Thanks,
谢谢，
回答by symcbean
If you were moving house from the USA to Canada would you go via Japan?
如果你要从美国搬家到加拿大，你会走日本吗？
While the differences between Oracle and other SQL rDBMS are even more pronounced that the differences between other SQL rDBMS, the shortest route seems to be from one database to database. I'd go with exporting the schema in SQL DDL to a text file so it can be manually tinkered with, then use a program to move the actual data across - e.g. using odbc links. I reckon the latter could be written in about 100 lines of code assuming that its not available off the shelf.
虽然Oracle 和其他SQL rDBMS 之间的差异比其他SQL rDBMS 之间的差异更加明显，但最短的路线似乎是从一个数据库到另一个数据库。我会将 SQL DDL 中的模式导出到文本文件，以便可以手动修改它，然后使用程序来移动实际数据 - 例如使用 odbc 链接。我认为后者可以用大约 100 行代码编写，假设它不是现成的。
Alternatively, since you don't seem to have decided on a DBMS yet, you might want to have a look at Enterprise DB (based on PostgreSQL) which comes with tools for migrating from Oracle.
或者，由于您似乎还没有决定使用 DBMS，您可能想看看 Enterprise DB（基于 PostgreSQL），它带有从 Oracle 迁移的工具。
C.
C。
回答by Roland Bouman
My advice:
我的建议：
- always make an export so you can re-import (with IMP) into oracle again. EXP documentation: http://wiki.oracle.com/page/Oracle+export+and+import+
- use a tool like Data Architect to migrate the schema (http://www.sqlpower.ca/page/architect). This tool allows you to import the structure of the Oracle DB, and then make a comparison with a target schema. It can then genereate and/or execute a script to reconcile structural changes. Although this will help you define the table DDL and indexes (and unique constraints and primary key constraints) you will most certainly need to manually modify the target model. Choosing the right data type is always something you need to consider - i disrecommend automated data type mapping. Foreign keys can be a challenge too when going to MySQL. For that particular case you need to ensure you are moving the data to InnoDB tables.
- use a tool like kettle aka pentaho data integration (http://kettle.pentaho.org/download here http://sourceforge.net/projects/pentaho/files/) to actually move the data. kettle includes a wizard to copy all table contents. I believe data architect can do migration too, but I prefer using kettle
- 始终进行导出，以便您可以再次（使用 IMP）重新导入 oracle。EXP 文档：http: //wiki.oracle.com/page/Oracle+export+and+import+
- 使用 Data Architect 之类的工具来迁移架构 ( http://www.sqlpower.ca/page/architect)。该工具允许您导入 Oracle DB 的结构，然后与目标模式进行比较。然后它可以生成和/或执行脚本来协调结构变化。尽管这将帮助您定义表 DDL 和索引（以及唯一约束和主键约束），但您肯定需要手动修改目标模型。选择正确的数据类型始终是您需要考虑的事情 - 我不推荐自动数据类型映射。使用 MySQL 时，外键也可能是一个挑战。对于这种特殊情况，您需要确保将数据移动到 InnoDB 表。
- 使用像kettle aka pentaho 数据集成（http://kettle.pentaho.org/在这里下载http://sourceforge.net/projects/pentaho/files/）这样的工具来实际移动数据。水壶包括一个复制所有表格内容的向导。我相信数据架构师也可以进行迁移，但我更喜欢使用kettle
These EXP/IMP tools are part of oracle. They are in the bin dir of your oracle server install. The other tools are FOSS - (free software)
这些 EXP/IMP 工具是 oracle 的一部分。它们位于您的 oracle 服务器安装的 bin 目录中。其他工具是 FOSS -（免费软件）
Another tool that might help you is Oracle's own SQL developer
另一个可能对您有帮助的工具是 Oracle 自己的 SQL 开发人员
