Migrate Data from SQL Server to Oracle - Database Tour Documentation

This topic describes how to export data from Microsoft SQL Server database to Oracle database in Database Tour.

Export Preparation

It is not mandatory but it is recommended to register both the source and the target databases before you start to configure your export process.

Both databases must support the same architecture: 32-bit or 64-bit. I.e. both databases must be successfully opened in one Database Tour instance.

It is recommended to use FD interface on both sides.

Export Steps

Exporting a Dataset

Open the source SQL Server database.
Choose a dataset to export. I.e. select a table in the table list or write and execute your SQL query.
If you chose the table, switch to Data page.
Click Export button.
Switch to the Database tab and select your registered Oracle database as a target database.
Specify a target table.
Please carefully review the following export options, as they might affect the export speed significantly:
- Memory saving mode. It is recommended to choose it.
- Use SQL Parameters. It is recommended to choose it.
- Batch mode. It is recommended to choose it.
- Commit interval. The bigger its value, the faster your exporting process. But too big value may cause memory and other issues. Therefore, try to play with it to find the optimal value before porting the solution to your production environment.
If your dataset is extra large, please consider our recommendations for exporting large datasets.
Choose the needed Export mode and other options:
Click Next. Check the source-to-target field mappings, i.e. the correspondence between source and target fields (columns).
It is possible to specify full column specifications and descriptions for target columns here. They will be applied when the the target table does not exist or needs recreating.
You can skip this step by clicking Export instead of Next; in this case the field mappings will be built implicitly according to column naming rules and available column types of the destination database.
Click Export.

Exporting Multiple Tables

Open the source SQL Server database.
Switch to the Tables tab to the left. Right-click the table list and choose Multiple Selection.
Select the tables to export.
Click Export button.
Switch to the Database tab.
From the list of registered databases, select the destination Oracle database.
Specify needed export options (make sure to choose correct export mode).
Leave the Table name field empty; otherwise, the data will be exported to one destination table and you must make sure all the source tables has the same or very similar structure.
Click Next and specify the source-to-target table mappings, i.e. the correspondence between source datasets and target tables.
It is possible to specify full column specifications for target tables here.
SQL Server and Oracle support table and column descriptions, and you can specify them here too; use <copy_from_source> macro to force the application to copy the descriptions from the source tables (if any) to target tables. Note that descriptions are applied during the table creation only.
You can skip this step by clicking Export instead of Next; in this case the table mappings will be built implicitly according to naming rules of the destination database.
Click Export.

In case of performance problems: try different combinations of Memory saving and Commit interval options; for large tables, use these recommendations.

Migrating Data via Command Line

Notes

This functionality is available in Database Tour Pro.
A basic command line can be generated automatically by choosing Tools | Build Command Line | Export / Import Data... menu.
The basic command line and example below can be improved by adding other command line switches and parameters according to the documentation.

SQL Server to Oracle Exporting from Command Line

The command line for exporting multiple tables from SQL Server to Oracle looks like this:

dbtour.exe /silent /export /ExportType=DATABASE /ExportMode=REPLACE+INSERT /CommitInterval=200 /SrcDBInterface=FD /SrcDBKind=DSN "/SrcDBDriver=SQL SERVER" /SrcServer=localhost\SQLEXPRESS /SrcDB=master /SrcDBUserName=sa /SrcDBPassword=sa_password /TrgDBInterface=FD /TrgDBKind=DSN /TrgDBDriver=ORACLE /TrgOSAuthentication=Yes /TrgAuthenticationMode=Normal /TrgVendorHomepath=C:\app\John\product\12.1.0\client_1 /TrgDB=cloud_prod "/TableMappingsFile=c:\DWH\Export\SQL-Server-To-Oracle-Table-Mappings.xml" /LogFile=C:\DWH\Export\Log\export.log /AppendLog

SQL Server to Oracle Exporting by Action File

The command line above can be transformed to an action file, which is much more comfortable to work with. The action file contains all the command line parameters (except /silent switch), one parameter per line, and allows comments:

/export 
/ExportType=DATABASE 
/ExportMode=REPLACE+INSERT ;Target tables recreated when exist
;/ExportMode=APPEND ;Incoming records are appended to target tables
/CommitInterval=1000 ;COMMIT after exporting every 1000 records
/SrcDBInterface=FD 
/SrcDBKind=DSN 
/SrcDBDriver=SQL SERVER 
/SrcServer=localhost\SQLEXPRESS 
/SrcDB=master 
/SrcDBUserName=sa 
/SrcDBPassword=sa_password 
/TrgDBInterface=FD 
/TrgDBKind=DSN 
/TrgDBDriver=ORACLE 
/TrgOSAuthentication=Yes 
/TrgAuthenticationMode=Normal 
/TrgVendorHomepath=C:\app\John\product\12.1.0\client_1 
/TrgDB=cloud_prod 
/TableMappingsFile=c:\DWH\Export\SQL-Server-To-Oracle-Table-Mappings.xml 
/LogFile=C:\DWH\Export\Log\export.log 
/AppendLog

The action file can be executed either from Database Tour GUI, in Open Data Source dialog, or from the command line like this:

dbtour.exe /silent /ActionFile=c:\DWH\Export\ExportActionMsSqlOra.txt

Example of SQL Server to Oracle Table Mappings File

The table mappings file contains the correspondence between source and target table names. Optionally, each table pair can include nested field mappings, i.e. the correspondence between source and target columns. The following example contains the table mappings for three tables. One of them contains nested field mappings; for others tables, the field mappings will be built automatically. It is recommended that you specify table and field mappings explicitly.

You can create such files either in the Export dialog on the Table Mappings step or manually using a text editor.

<?xml version="1.0" encoding="UTF-8"?>
<!-- Source-to-Target table mappings for master database --> 
<TableMappings>
  <Items>
    <TableMapping>
      <Source>
        <TableName>dbo.customer</TableName>
      </Source>
      <Target>
        <TableName>DWH.CUSTOMER</TableName>
        <TableDescription>Customers</TableDescription>
      </Target>
      <FieldMappings>
        <Items>
          <FieldMapping>
            <Source>
              <FieldName>cust_it</FieldName>
            </Source>
            <Target>
              <FieldName>CUSTOMER_ID</FieldName>
              <FieldType>INTEGER</FieldType>
              <FieldNotNull>1</FieldNotNull>
              <FieldDefaultValue></FieldDefaultValue>
              <FieldSQLSpecification>INTEGER</FieldSQLSpecification>
              <FieldDescription>Internal customer identifier</FieldDescription>
            </Target>
          </FieldMapping>
          <FieldMapping>
            <Source>
              <FieldName>cust_name</FieldName>
            </Source>
            <Target>
              <FieldName>CUSTOMER_NAME</FieldName>
              <FieldType>VARCHAR2</FieldType>
              <FieldSize>60</FieldSize>
              <FieldNotNull>1</FieldNotNull>
              <FieldDefaultValue></FieldDefaultValue>
              <FieldSQLSpecification>VARCHAR2(60)</FieldSQLSpecification>
              <FieldDescription>Full customer name</FieldDescription>
            </Target>
          </FieldMapping>
        </Items>
      </FieldMappings>
    </TableMapping>
    <TableMapping>
      <Source>
        <TableName>dbo.order</TableName>
      </Source>
      <Target>
        <TableName>DWH.ORDER</TableName>
        <TableDescription>Orders</TableDescription>
      </Target>
    </TableMapping>
    <TableMapping>
      <Source>
        <TableName>dbo.payment</TableName>
      </Source>
      <Target>
        <FileName>DWH.PAYMENT</TableName>
        <TableDescription>Payments</TableDescription>
      </Target>
    </TableMapping>
  </Items>
</TableMappings>

See also

Opening SQL Server Databases

Opening Oracle Databases

Exporting Data

Command Line Usage: Export / Import Data

Field Mappings File

Table Mappings File

Action File