This discussion is archived
9 Replies Latest reply: Oct 3, 2013 6:17 AM by TSharma-Oracle RSS

oracle 10g expdp

Rabi786 Newbie
Currently Being Moderated

Hi

 

My database size is around 2900 GB in  AIX 6.1, database version is 10.2.0.3.

 

Everyday I need to take expdp dump backup of a single table which is only 57 MB in size. It takes around 55 minutes to complete the dump backup. I have noticed that when backup starts , in first phase it does table scan ( we have 330000 tables) , next purely backup begin.

 

My query is , 1. how to make first my dump backup?

                    2. is there any way to skip table scan ?

 

 

Regards

 

Jewel

  • 1. Re: oracle 10g expdp
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated

    Pl post the expdp command you are using and the complete log file from the last export

     

    HTH
    Srini

  • 2. Re: oracle 10g expdp
    TSharma-Oracle Guru
    Currently Being Moderated

    Please post the definition of the table also along with expdp command.

  • 3. Re: oracle 10g expdp
    Rabi786 Newbie
    Currently Being Moderated

    Hi Srini

     

    Log is as under :

     

    ;;;

    Export: Release 10.2.0.1.0 - 64bit Production on Tuesday, 01 October, 2013 17:41:57

     

     

    Copyright (c) 2003, 2005, Oracle.  All rights reserved.

    ;;;

    Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

    With the Partitioning, OLAP and Data Mining options

    Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/********@db1 directory=dp_data dumpfile=data_011013_1741.dmp tables=prod.data1 logfile=data_log_011013_1741.log exclude=statistics

    Estimate in progress using BLOCKS method...

    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

    Total estimation using BLOCKS method: 57.06 MB

    Processing object type TABLE_EXPORT/TABLE/TABLE

    Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

    Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

    . . exported "prod"."data1"                        35.53 MB  172629 rows

    Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

    ******************************************************************************

    Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:

      /u01/dp_data/data_011013_1741.dmp

    Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 19:09:07

  • 4. Re: oracle 10g expdp
    Rabi786 Newbie
    Currently Being Moderated

    Hi

     

    Table definition :

     

    SQL> desc prod.data1;

    Name                                      Null?    Type

    ----------------------------------------- -------- ----------------------------

    RECID                                     NOT NULL VARCHAR2(200)

    XMLRECORD                                          XMLTYPE

     

     

    system/********@db1 directory=dp_data dumpfile=data_011013_1741.dmp tables=prod.data1 logfile=data_log_011013_1741.log exclude=statistics

  • 5. Re: oracle 10g expdp
    TSharma-Oracle Guru
    Currently Being Moderated

    This seems to be a bug in 10.2.0.3. Ask Oracle support for more help.

    Bug 5929373 - Export Data Pump of a table can be very slow if database has many user tables.


    Also, you can try' parallel' clause. It might help in scanning through the tables fast.

  • 6. Re: oracle 10g expdp
    MohaAGOU Explorer
    Currently Being Moderated

    Hi,

     

    it take around 88 minutes in the logs for 36M !

     

    for the first question : you can set parallel option to value of CPU in your machine.

    for the second : i don't think you can do it  without a table scan, expdp or exp need to read all the table to export it.

  • 7. Re: oracle 10g expdp
    Richard Harrison . Expert
    Currently Being Moderated

    Hi,

    Why does the expdp header show 10.2.0.1? Are you using a different installation to do the export than the one running the database - both should show 10.2.0.3?

     

    Regards,

    Harry

  • 8. Re: oracle 10g expdp
    Rabi786 Newbie
    Currently Being Moderated

    Hi MohaAGOU

     

    I used parallel but did't get any benefit. So far I export single table that is why parallel does't work.

  • 9. Re: oracle 10g expdp
    TSharma-Oracle Guru
    Currently Being Moderated

    Again check and ask Oracle support about this

     

    Bug 5929373 - Export Data Pump of a table can be very slow if database has many user tables.


    And you have 330000 tables.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points