EXPORT with Tables having CLOB datatype is SLOW
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: EXPORT with Tables having CLOB datatype is SLOW

  1. #1
    Join Date
    Apr 2006
    Posts
    23

    EXPORT with Tables having CLOB datatype is SLOW

    Hi All Gurus,
    Recently we changed Datatype from LONG to CLOB in our database tables. However when we are doing a export/Import of Schema it is long long time to complete .I do not understand as per
    Oracle's documentation Oracle recommends change the datatype from LONG to CLOB.However EXP/IMP of those tables with CLOB takes for ever. Any Idea how to make it fast.
    We are on Oracle 9.2.0.6.0
    Any help on above is highly appreciated.
    Regards

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    for ever huh? how scientific

  3. #3
    Join Date
    Apr 2006
    Posts
    23
    Any update and advice on above.
    Regards

  4. #4
    Join Date
    Apr 2006
    Posts
    23
    Quote Originally Posted by davey23uk
    for ever huh? how scientific
    Any update and advice on above issue.

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    yeah, do some investigation and provide some analysis

  6. #6
    Join Date
    Apr 2006
    Posts
    23
    Quote Originally Posted by davey23uk
    yeah, do some investigation and provide some analysis
    We have table called ABC_TEXT with one columns as "MSG" and the data type was LONG I converted the LONG column to CLOB as given below.
    Alter table ABC_TEXT modify msg clob;
    Number of row were equal after modification i.e. "4500000" appox.
    When I am exporting this table it took 3.25 Hours. That is my problem and export command is given below

    exp system@abcvora1 tables=abc_TExT Direct=y file=C:\ABS.bak

    kindly advice.

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    doesnt seem to bad, still no details though

    What are you waiting on, what analysis of the situation have you done

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Run the export command with suitable value for RECORDLENGTH parameter.

    The max value is 64KB.

    Also, increase buffer size.

    Tamil

  9. #9
    Join Date
    Apr 2006
    Posts
    23
    Quote Originally Posted by davey23uk
    doesnt seem to bad, still no details though

    What are you waiting on, what analysis of the situation have you done
    Let me know what details should I mail to you.

  10. #10
    Join Date
    Apr 2006
    Posts
    23
    Quote Originally Posted by tamilselvan
    Run the export command with suitable value for RECORDLENGTH parameter.

    The max value is 64KB.

    Also, increase buffer size.

    Tamil
    Thanks for the tips, that were really helpful I exported with the same table with following syntax and export took 36mins.
    WAY-1
    Exp system@INTORA1 TABLES= (COL.ABC_TEXT) DIRECT=Y RECODELENTH=65335 FILE=C:\ABC_TEXT.dmp

    WAY-2
    Exp system@INTORA1 TABLES= (COL.ABC_TEXT) FILE=C:\ABC_TEXT.dmp BUFFER=37748736

    Following is a note from Oracle Doc:
    "The Buffer parameter applies only to conventional path export. It has no effect on a direct path Export. For direct path exports use RECORDLENTH parameter to specify the size of the buffer that Export uses for writing to the export file."

    Both the above ways took 36 mins to complete. Just want to confirm which of the above is correct or both are correct.

    First can you throw some light when you mentioned "Increase BUFFER SIZE"

    Second How can I make Import process faster for above table.

    You reply on above is highly appreciated.

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