将数据从SQL Server重复移动到Oracle

What are the most reasonable ways to move table data from SQL Server to Oracle (on *nix) on a regular basis?

有哪些最合理的方法可以定期将表数据从SQL Server移动到Oracle（在* nix上）？

6 个解决方案

#1

Using SQL Server Integration Services (SSIS) is likely your best bet. If you're not familiar with SSIS, the best way to try something out is to use the SQL Server Export Wizard and have it create an SSIS package for you. For example, if you go into SQL Server Management Studio and right-click on your database, then select Tasks->Export Data. From there click next until you get to the "Choose a Destination" step. Select the "Microsoft OLE DB Provider for Oracle" and click Properties to define your database connection. When you click through the wizard, on the Save and Execute page, make sure you check the checkbox labelled "Save SSIS Package", on the next screen specify where to save the SSIS package. Once you finish the Export Wizard, your data will have been exported and you will have an SSIS package that you can use as is, or go in and tweak it to do more specific things. Once you have your SSIS package, you can schedule it by creating a SQL Server Agent Job.

使用SQL Server Integration Services（SSIS）可能是您最好的选择。如果您不熟悉SSIS，尝试解决问题的最佳方法是使用SQL Server导出向导并让它为您创建一个SSIS包。例如，如果您进入SQL Server Management Studio并右键单击数据库，则选择“任务” - >“导出数据”。从那里单击下一步，直到进入“选择目的地”步骤。选择“Microsoft OLE DB Provider for Oracle”，然后单击“属性”以定义数据库连接。单击向导时，在“保存并执行”页面上，确保选中“保存SSIS包”复选框，在下一个屏幕上指定保存SSIS包的位置。完成导出向导后，您的数据将被导出，您将拥有一个可以按原样使用的SSIS包，或者进入并调整它以执行更具体的操作。获得SSIS包后，可以通过创建SQL Server代理作业来安排它。

#2

• Oracle Heterogeneous Connectivity / Database Gateways in conjunction with materialized view(s), PL/SQL, or Java
• Oracle异构连接/数据库网关与物化视图，PL / SQL或Java结合使用
• SSIS or DTS: both can be scheduled but require more than read-only access to SQL Server
• SSIS或DTS：两者都可以调度，但需要的只是对SQL Server的只读访问权限
• Java (probably within Oracle but optionally at the OS) using ODBC or SQLJ to access SQL Server and, possibly, Oracle
• Java（可能在Oracle中，但可选择在操作系统中）使用ODBC或SQLJ访问SQL Server，可能还有Oracle
• SQL Server scheduled to export to CSV, Oracle scheduled to import from CSV
• SQL Server计划导出为CSV，Oracle计划从CSV导入
• Any of the other ETL tools (e.g. Informatica, Cognos)
• 任何其他ETL工具（例如Informatica，Cognos）
• Any of the myriad languages that can access both databases (but would require maintaining a third environment to run the application within)
• 可以访问两个数据库的任何无数语言（但需要维护第三个环境才能在其中运行应用程序）

Scheduling:

调度：

• Automatic / not reuqired with materialized views
• 自动/不需要物化视图
• Oracle DBMS_JOB / DBMS_SCHEDULER
• Oracle DBMS_JOB / DBMS_SCHEDULER
• OS-specific (cron, Windows Scheduled Tasks, etc)
• 特定于操作系统（cron，Windows计划任务等）
• In the case of SSIS, DTS, or CSV export, scheduled within SQL Server
• 对于在SQL Server中安排的SSIS，DTS或CSV导出的情况

#3

You can have SQL Server interface with Oracle directly through SSIS (or DTS for 2k). It will provide ETL functionality and can be scheduled on a regular basis.

您可以通过SSIS（或DTS for 2k）直接与Oracle建立SQL Server接口。它将提供ETL功能，并可定期安排。

#4

Create a database link from Oracle to Sql Server (heterogeneous connectivity). You can use this link to retrieve the data from Sql Server with a simple select statement. If you want to schedule you can use a materialized view or dbms_scheduler.

创建从Oracle到Sql Server的数据库链接（异构连接）。您可以使用此链接通过简单的select语句从Sql Server检索数据。如果要安排，可以使用物化视图或dbms_scheduler。

An alternative is to put your data in a csv file, you can use an external table or sqlloader to load this data in the Oracle database.

另一种方法是将数据放在csv文件中，可以使用外部表或sqlloader在Oracle数据库中加载这些数据。

#5

I had success creating a linked server (from within the Enterprise Manager, I think) to Oracle on the SQL Server side. Then I could use normal stored procedures on both sides to accomplish smaller data movement and updates in both directions. This approach can bypass the need to try and put something together outside the databases.

我成功地在SQL Server端创建了一个链接服务器（从企业管理器中，我认为）到Oracle。然后我可以使用两侧的常规存储过程来完成较小的数据移动和两个方向的更新。这种方法可以绕过尝试在数据库之外放置一些东西的需要。

Try hard to use the latest possible Oracle client on the SQL Server side though. I recall some defects in the 10.2.0.2 client and getting the 10.2.0.4 client required your "official" Oracle registration or purchase number or something.

尽量在SQL Server端尽量使用最新的Oracle客户端。我记得10.2.0.2客户端的一些缺陷，并且10.2.0.4客户端需要您的“官方”Oracle注册或购买号码等。

For big data moves (or maybe even moves/updates you want done on a daily or less frequent basis), definitely use one of the ETL tools. We had Informatica for our ETL processes, but if SSIS can pull off what you need, that's fine too.

对于大数据移动（或者甚至是您希望每天或不太频繁地移动/更新），请务必使用其中一个ETL工具。我们的ETL流程有Informatica，但如果SSIS可以满足您的需求，那也没关系。

#6

Here is what I do: Connect to SQL Server by Oracle SQL developer using this link: https://kentgraziano.com/2013/01/14/tech-tip-connect-to-sql-server-using-oracle-sql-developer/

以下是我的操作：Oracle SQL开发人员使用以下链接连接到SQL Server：https：//kentgraziano.com/2013/01/14/tech-tip-connect-to-sql-server-using-oracle-sql-开发商/

After you have added SQL jar, you will see SQL Server tab in the Connection window:

添加SQL jar后，您将在“连接”窗口中看到“SQL Server”选项卡：

Then connect to the SQL Instance.

然后连接到SQL实例。

Then open SQL instance and choose database or table that you want to copy. Right click on any database/table, then click on "copy to oracle" there and choose the right user[database] at "Destination Connection Name " where you want to copy your tables.

然后打开SQL实例并选择要复制的数据库或表。右键单击任何数据库/表，然后单击“复制到oracle”，在“目标连接名称”中选择要复制表的正确用户[数据库]。

You can also change some properties there. Click "OK" and that's it.

您还可以在那里更改一些属性。单击“确定”即可。

Let me know in case of any issues.

如有任何问题，请告诉我。