DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Improving the imp speed

  1. #1
    Join Date
    Feb 2004
    Location
    Chennai, India
    Posts
    7

    Improving the imp speed

    Hi,

    I am having a table which is having around 11 million records. Now I have to take this table from one DB to another one so I am using exp and imp. But we want to use SQLLoader so how to extract the tables into flat files using PL/SQL. Any Help?

    Regds

    Krish

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    well dont have to use plsql search for flat on asktom.oracle.com

    use perl

    or spool from sqlplus concatenating the columns together.

    use utl_file to write to an os file concatting the columns together.

    However 11 million records isnt very much at all, imp shouldnt take too long to do that, few minutes i reckon

  3. #3
    Join Date
    Apr 2003
    Posts
    353
    If a dblink can be provided, use create table as select
    or copy command.. the fastest way..

  4. #4
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    hi hun,

    So far no one beats sqlloader direct path speed in loading data,
    so if u knew ur data well yo can spool it using..

    edit spool1.sql
    ---------------
    set echo off
    set pages 0
    set feedback off
    set verify off
    set term off
    set heading off
    spool TABLE_NAME.TXT
    select '~'||column1||'~'||column2||'~'...||'~' from table_name;
    spool off
    exit

    if date format is not the default use to_char(date_column,'yyyymmdd hh:mi:ss')

    use the .ctl delimited by '~'

    I often use this but not tried with tables having images saved

  5. #5
    Join Date
    Apr 2003
    Posts
    353
    Direct load
    Spooling time + loading time

    import
    Export +import time

    Create table or copy table through link
    only the create table or copy table time.
    (use nologging option..)

    Which one you prefer if you can have a dblink??

  6. #6
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    Originally posted by engiri
    Direct load
    Spooling time + loading time

    import
    Export +import time

    Create table or copy table through link
    only the create table or copy table time.
    (use nologging option..)

    Which one you prefer if you can have a dblink??
    Direct load.
    -nagarjuna

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Obviously, the answer is to understand and benchmark the different processes, and decide which makes better sense in your own environment based on performance and manageability.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Jan 2001
    Posts
    3,134
    db_link is a variable, we have db_links that link boxes in NJ to boxes in Singapore, obviously this type of mileage can slowdown network traffic. So using a db_link is not always the best solution.

    Personally I would exp, zip, FTP, import.
    I remember when this place was cool.

  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by Mr.Hanky

    Personally I would exp, zip, FTP, import.
    You forgot "Use a parfile".
    Jeff Hunter

  10. #10
    Join Date
    Nov 2003
    Posts
    89
    Import the table with indexes=n

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