I have to import 36.8 million records (which are currently in multiple MS Access tables) into my 8i database. Does anyone have any ideas on the fastest way to perform the import? Are there things that I can do to the receiving table to improve performance andmake the import go as quickly as possible?
1. Take a text file of the tables and then use loader to import the tables into Oracle
2. Configure Oracle Migration with your version of SQL DB.
3. Configure Transparent Gateway and get your data by SQL Statements.
Use sqlldr with direct path load
Keep the db in NOARCHOVELOG mode
If you plan to start multiple sqlldr session, design the FREELISTS and INITRANS for the table properly
Specify large enough extent sizes so that extent management doesn't happen too many times during the process
Do not have any indexes on this table to start with. Build indexes after the load is done
Monitor online redo log contention
The sqlldr looks for a data file and a control file that identifies the design of the table I'm importing into. So I first did an unload of the table to get the CTL file and then exported from Access into a text file. When I try to load the data I get an ORA-24329: invalid character set identifier. Where is it trying to identify the character set?
SQL*Loader: Release 220.127.116.11.0 - Production on Thu Jun 13 15:12:27 2002
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Control File: D:\Data\CONVER~3.CTL
Data File: D:\Data\QTR1_2~1.DAT
Bad File: D:\Data\CONVER~1.BAD
Discard File: D:\Data\CONVER~1.DSC
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: Direct
Table CONVERSION_SUPPLY_TABLE, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DESCRIPTION FIRST 255 ~ O(') CHARACTER
PRODUCT_EAN NEXT 50 ~ O(') CHARACTER
ISSUE_DATE NEXT 22 ~ O(') CHARACTER
RETAILER_ID NEXT 25 ~ O(') CHARACTER
WHOLESALER_ID NEXT 25 ~ O(') CHARACTER
PRODUCT_ID NEXT 5 ~ O(') CHARACTER
SUPPLIED NEXT * ~ CHARACTER
RETURNED NEXT * ~ CHARACTER
START_DATE NEXT 22 ~ O(') CHARACTER
END_DATE NEXT 22 ~ O(') CHARACTER
DAY_OF_WEEK NEXT * ~ CHARACTER
SQL*Loader-951: Error calling once/load initialization
ORA-24329: invalid character set identifier
you can datatype conversion issues with Migtation Toolkit and Gateways. Using gateways it took 10 min per million rows on a 100 mbps network.
I'm very new to the Oracle world, can you explain more about the Migtation Toolkit and Gateways or point me to some literature on it? Gateways sounds like it would do the trick if I can get it installed and configured properly.
Set the NLS_LANG variable at OS level before using direct load. Set it to whatever is you db character set.
You can derive this by means of three nls variables that you can find from v$nls_parameters. These are NLS_LANGUAGE, NLS_TERRITORY and NLS_CHARACTERSET.
The actual value is "NLS_LANGUAGE"_"NLS_TERRITORY"."NLS_CHARACTERSET"
there's a way of importing data from MSAccess to Oracle db by using the ODBC link but i dont know the performance of such kind of import with large data. But its a lot easier.