Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2007
    Posts
    4

    Question Unanswered: export table - performance issue

    Hi,

    I'm trying to export a table containing around 7 Lakh records from a schema. The table structure is given below.

    Column Name Null? Data Type

    NODE_ID N INTEGER
    REGION_TYPE_ID N INTEGER
    LOCALE_ID N INTEGER
    DISPLAY_TEXT Y VARCHAR2 (1023 Char)
    PROD_DIFF Y CLOB
    FILE_NAME N VARCHAR2 (2000 Char)
    LAST_MODIFICATION_TIME N DATE

    The problem is that its exporting via conventional path even with direct=y option. Also its has taken more than 24 hours and still its going on. Can anybody help speed up the process. The command I used is given below.

    exp userid=UN/PW@DB TABLES=(sch1.t1) file=c:/t1.dmp direct=y recordlength=50000 feedback=1000

    I would also like to know why the export automatically got switched to conventional path with direct=y option.

    --Raja

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Reason its conventional is because of the CLOB.

    1) Specify the buffer size i.e. buffer=10000000
    2) The export will be quicker if its run on the database server (cutting out the network)
    3) Make sure the export file isnt created on a disk which has the databases data files on it (reduce contention)
    4) If its 10g use expdp instead
    5) If not you could use a query to cut down on the result set, then set of multiple exports off in parallel doing different parts of the table.
    6) Look at the CLOB storage parameters you may be able to optimise this.

    Alan

  3. #3
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by db.raja
    around 7 Lakh records
    Pardon my ignorance, but what are "7 Lakh records"?

  4. #4
    Join Date
    Jul 2007
    Posts
    4
    Hi Alan,

    Thanks a lot . Its quite okay now with buffer option.

    Hello Shammat,

    I meant its 7,00,000 rows in that table.

    --Raja

Posting Permissions

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