如何将数据从 SQL Server 数据库传输到 Oracle 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5101362/
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
How to transfer data from a SQL Server Database to a Oracle Database
提问by gizgok
The current application I'm working lets call X is an archiving application for the data kept another application say Y. Both are very old applications developed about 8 odd years back. So far in my reading of the documentation, I have learnt that the process to transfer data used is that, the SQL Server Database Tables snapshot is created in flat files and then this flat files are ftp'd to the correct unix box where through ctl various insert statements are generated for the Oracle Database and that's how this data is transferred. It uses bcp utility. I wanted to know if there is a better and a faster way this could be accomplished. There should be a way to transfer data directly, I feel the whole process of taking it in files and then transfer and insert must be really slow and painstaking. Any insights???
我正在工作的当前应用程序让我们调用 X 是一个存档应用程序，用于保存另一个应用程序说 Y 的数据。两者都是大约 8 多年前开发的非常古老的应用程序。到目前为止，在我阅读文档的过程中，我了解到传输数据的过程是，SQL Server 数据库表快照是在平面文件中创建的，然后将这些平面文件通过 ctl ftp 传输到正确的 unix 框为 Oracle 数据库生成了各种插入语句，这就是这些数据的传输方式。它使用 bcp 实用程序。我想知道是否有更好更快的方法可以实现这一点。应该有直接传输数据的方法，我感觉整个过程都是把文件取进去然后传输插入的过程，真的很慢很辛苦。有什么见解？？？
采纳答案by Sathyajith Bhat
Create a DB Linkfrom your Oracle Database to SQL Server database, and you can transfer the data via selects / inserts.
创建从 Oracle 数据库到 SQL Server 数据库的 DB Link，您可以通过选择/插入传输数据。
Schedule the process using DBMS_SCHEDULERif this needs to be done on a periodic basis.
如果需要定期执行此操作，请使用DBMS_SCHEDULER 来安排进程。
回答by ik_zelf
you can read data from a lot of different database vendors using heterogeneous services. To use this you create a service on the Unix box that uses - in this case - odbc to connect to the SQL Server database.
您可以使用异构服务从许多不同的数据库供应商读取数据。要使用它，您需要在 Unix 机器上创建一个服务，该服务使用 - 在本例中 - odbc 连接到 SQL Server 数据库。
You define this service in the listener.ora and you create a tns alias that points to this service. The alias looks pretty normal, except for the extra line (hs = ok). In your database you make a database link that using this tns alias as connect string.
您在 listener.ora 中定义此服务并创建指向此服务的 tns 别名。别名看起来很正常，除了额外的行 (hs = ok)。在您的数据库中，您创建了一个使用此 tns 别名作为连接字符串的数据库链接。
UnixODBC in combination with the FreeTDS driver works fine. The exact details vary between releases, for 10g look for hs4odbc, 11g dg4odbc.
UnixODBC 与 FreeTDS 驱动程序结合工作正常。不同版本的确切细节有所不同，对于 10g 查找 hs4odbc，11g dg4odbc。
