[Original] considerations for Oracle Data Import using SQL Server DTS

In the process of importing data from SQL to oracle, everyone's mindset is to use SQL Server's dtswizard first, because this tool is intuitive and convenient to use, in addition, non-large tables have good performance. I also did this, but after a series of painful operations, I found that there are some important restrictions in the use process.

In the process of importing data from SQL to oracle, everyone's mindset is to use SQL Server's dtswizard first, because this tool is intuitive and convenient to use, in addition, non-large tables have good performance. I also did this, but after a series of painful operations, I found that there are some important restrictions in the use process.

InImportIn oracle's work, everyone's mindset is to use SQL Server's dtswizard first, because this tool is reallyUseIt is intuitive and convenient, and has good performance in non-large table operations. I also did this, but after a series of painful operations, I found thatUseThere are still some very important restrictions in the process. Many people may encounter one or two of them. Here I only record what I encountered for reference.

I. general operation procedures

1. select SourceDataType. Here is SQL,Note:DataSource, in-time reminder,Note:DataSource, because he makes me heartache

2. Select the targetDataSource. It should be pointed out that I chose the wrong one at the beginning.DataSource, let me do the work for a week, or let me open up not applicable to dtswizard GuideDataAlthough it is very painful, it is very obvious. Speak nonsense,Note:Select "oracle Provideer for ole db ",Note:There is also a "Microsoft OLEDB for Oracle", which is the second one I have selected before, resulting in many strange errors when performing the export operation. Be sure to select the correct one.

3. Fill in the corresponding oracle information,Note:DataSource locationUseThe name specified in transnames. ora. You can select to save the password.

4. Click Next by default to go to The ing process of the table. Here we will perform corresponding processing for the table fields.

5. Select the corresponding table and click the edit button to go to "Edit SQL". You can manually adjust the fieldDataType.

Here, Microsoft has done a good job. It stores the SQLDataType and Other TypesDataMatching of corresponding fields in databases such as oracle sysbase db2

SELECT * FROM msdb. dbo. MSdatatype_mappings you can find the matching field type here.

6. Next, pray for success.

II,Note:Items

1.Data TransmissionDataThe support is not very good. Big TableDataCannot be simple fromDataVolume definition, some tablesDataLarge, but few columns, dts can still be executed normally. The standard I tested here is 0.8 millionDataThere are dozens of Columns with text fields in the middle. The execution efficiency is low first, which is good. After a long execution, an error is reported. AndDataIf the table is not successfully imported and created, you must manually delete the table before executing it again.ImportThe error in the middle of dozens of hundreds of tables is depressing.

The solution is:UseAlthough oracle SQL loader is troublesome, its efficiency is extremely high. The performance above 10000 +/s can be tricky.

2. Execute individual tablesImportUnsuccessful. Dizzy, I know what an exception is and can only export it in the most traditional way.DataAnd then use toadImportTo set the field type. You can click OK.

3. Set Fields: If varchar2 (2000) is exceeded here, an error is reported.DataType can only be manually adjusted to nclob type.

I think so much for the time being. I want to add more in the future.