Re: Data Migration from Access 2.0 to Oracle 7.3

I have found that the best way to do it is export the data out to a file, set up a control file and load it using SQL Loader. If you have thousands of records this is the fastest. You can use access with an ODBC connection to the oracle tables ONLY if they are small. The reasons is:

     If the there are alot of records the rollback segments on Oracle need to be very big to handle it because when using an ODBC link and a query to append the records, it is treated as one transaction. Aside from being slow it will max out your rollback segments and not add anything. Waste of time. I've tried using code in access with transactions but it is still slow.

In the same situation I created Access Code that would let me select a table in access and would export it out to a comma delimited file and read the column definitions to create the control file for me. I then copied the files to the server and ran SQL Loader to load the tables in direct mode. The reason I had Access create the control file for me is because it is a pain to have to type in all the field names for the control file. Especially if you have date fields which will have to be formatted in the control file for Oracle. Doing it this way, I was able to load thousands of records in seconds/minutes compared to hours.

kif_at_worldnet.att.net wrote in article
<5ut10b$n7n_at_bgtnsc02.worldnet.att.net>...
>
> Which is the optimum method of migrating data from Access to Oracle
 server.
>
> 1. SQL Loader
> 2. Using Access' export feature.
> 3. Other
>
> Thanks
>
  Received on Sun Sep 07 1997 - 00:00:00 CDT