DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: importing data into oracle

  1. #1
    Join Date
    Apr 2001
    Posts
    35
    Hi all,
    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?

  2. #2
    Join Date
    Apr 2002
    Location
    Phoenix, AZ
    Posts
    175
    1. Take a text file of the tables and then use loader to import the tables into Oracle

    or

    2. Configure Oracle Migration with your version of SQL DB.

    or

    3. Configure Transparent Gateway and get your data by SQL Statements.
    Sridhar R Patnam

  3. #3
    Join Date
    Jul 2000
    Posts
    521
    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
    svk

  4. #4
    Join Date
    Apr 2001
    Posts
    35
    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 9.2.0.1.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

  5. #5
    Join Date
    Apr 2002
    Location
    Phoenix, AZ
    Posts
    175
    you can datatype conversion issues with Migtation Toolkit and Gateways. Using gateways it took 10 min per million rows on a 100 mbps network.
    Sridhar R Patnam

  6. #6
    Join Date
    Apr 2001
    Posts
    35
    Thanks patnams,
    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.

  7. #7
    Join Date
    Jul 2000
    Posts
    521
    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"
    svk

  8. #8
    Join Date
    Feb 2002
    Posts
    54
    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.

    =======================
    Manifest plainness,
    embrace simplicity,
    reduce selfishness,
    have few desires. LAO TZU
    =======================

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width