9 Replies Latest reply: Oct 3, 2013 8:17 AM by TSharma-Oracle RSS

    oracle 10g expdp

    Rabi786

      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

          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

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

            • 3. Re: oracle 10g expdp
              Rabi786

              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

                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

                  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

                    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 .

                      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

                        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

                          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.