Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421

    Unanswered: Spool output or ?

    1)
    I need to get data out of Oracle, about a million rows (give or take a couple of thousand) .
    I have the data I need in a stored table, but... What is the most efficient way to get it out?

    Currently I am just spooling it to a file, but that takes for ever.... Is there a better way?

    2)
    Also a simular question, this time getting data into Oracle, I am uploading data from an Access database.
    Uploading about 10,000 records takes about 20 minutes, I know... It is about 8 per second, but
    - is this "normal"?
    - is it depending uppon network traffic?
    - is it faster when using SQLLoader?
    - other answers to questions I cannot think of right now, but related?

    Oh, I cannot access the Oracle server,
    dont know where it is located (win or unix)
    So I have to access it over the network, using either SQL*Plus, ODBC or possibly SQLLoader or Imp/Exp

    I hope you guys have some bright Ideas for me....

    Regards

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Spooling the result of a query into a file could be faster if you SET TERMOUT OFF before executing the query.
    TERM[OUT] {OFF|ON}

    Controls the display of output generated by commands executed from a command file. OFF suppresses the display so that you can spool output from a command file without seeing the output on the screen. ON displays the output. TERMOUT OFF does not affect output from commands you enter interactively.
    As of your second question: if it is about importing data from an Access database into Oracle, forget about EXP and IMP utilities as EXP can read only from Oracle and IMP can write only into Oracle.

    SQL*Loader might be faster if network traffic is heavy and prevents data flow. However, you would have to transfer a textual file (readable by SQL*Loader) from the remote computer to your location. Would it help if you perform such things during non-working hours (when everyone or most of the people are at home)?

  3. #3
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Thanks Littlefoot for responding.

    TERMOUT
    This makes for no output from scripts... Uhm... Good. So with scripts I ussually do a bat file that is schedulled.
    Quote Originally Posted by FILE.BAT
    @call C:\oracle\ora81\bin\sqlplus uasr/pw@schema @C:\Schedule\Script.sql
    Quote Originally Posted by SCRIPT.SQL
    SET ... ON/OFF
    Create some (temp) tables
    ... bla bla, etc, etc
    Create some (temp) functions
    spool C:\Output\script.txt
    select * from endproduct
    spool off
    I guess I could switch between TERMOUT anywhere to not show any output?
    In any case I would put SET TERMOUT OFF before the spool, Right?
    and I can turn it back ON after the spool should the script continue... Cool...

    IMP/EXP
    I kind off thought that was true, but thanks for the confirmation


    I went on after my post to do some research of my own.
    Loading 10000 records like I said takes 20 minutes (regardless of time and/or place) using access.

    SQL loader 10000 records in under 10 seconds (average of running the load 20 times) !!!

    What a difference... Loaded the million records in "no time"
    Now lets wait for the next step (query in Oracle) to finish creating its table.

    Thanks again, now if only I could get past the good old "Rollback segment too small" problem....

  4. #4
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Thanks Littlefoot for responding.

    TERMOUT
    This makes for no output from scripts... Uhm... Good. So with scripts I ussually do a bat file that is schedulled.
    Quote Originally Posted by FILE.BAT
    @call C:\oracle\ora81\bin\sqlplus uasr/pw@schema @C:\Schedule\Script.sql
    Quote Originally Posted by SCRIPT.SQL
    SET ... ON/OFF
    Create some (temp) tables
    ... bla bla, etc, etc
    Create some (temp) functions
    spool C:\Output\script.txt
    select * from endproduct
    spool off
    I guess I could switch between TERMOUT anywhere to not show any output?
    In any case I would put SET TERMOUT OFF before the spool, Right?
    and I can turn it back ON after the spool should the script continue... Cool...

    IMP/EXP
    I kind off thought that was true, but thanks for the confirmation


    I went on after my post to do some research of my own.
    Loading 10000 records like I said takes 20 minutes (regardless of time and/or place) using access.

    SQL loader 10000 records in under 10 seconds (average of running the load 20 times) !!!

    What a difference... Loaded the million records in "no time"
    Now lets wait for the next step (query in Oracle) to finish creating its table.

    Thanks again, now if only I could get past the good old "Rollback segment too small" problem....

  5. #5
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    The forum doesnt show my new post(s) so I will try to make it show now...

    Also somehow the same post is there twice... If any admin sees this, one of them double ones can definatly be deleted, tho I am sure I only posted it once.

    Greets & Thanks again

Posting Permissions

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