7 Replies Latest reply on Jun 10, 2011 3:12 PM by 650635

    expdp taking long time on one table

    650635
      Hi,

      I am trying to use data pump export to get a dump of a schema to copy across to another database. It's been working on one table now for 6 hours, which I think is a problem.

      Database is 10.2.0.4.0 on Windows 2003

      The table in question has 35,000,000 rows, with an average row length of 586, it takes up 25Gb (3million x 8k blocks).

      Looking at the waits, it's always just waiting on db file sequential read on one of the datafiles that holds the data for this table.

      I've just started at this company so I don't know much about the data, database structures etc. but in terms of size, number of blocks across datafiles etc. there's a very similar table in the database (it may even be a recent copy, the name is the same but with a _T suffix). If I expdp that table, it completes in under 10 minutes.


      Just wondering if anyone has any ideas on what might be wrong, where I can look for the cause of this?

      Cheers,

      Ados
        • 1. Re: expdp taking long time on one table
          Karan Kukreja
          Hello Ados ,

          To start with , please post your export script and the logfile which is getting generated. I believe you would have specified the logfile paramter in the export expdp script you have executed.

          Thanks
          Kk

          Edited by: Kk on Jun 2, 2011 5:33 AM
          • 2. Re: expdp taking long time on one table
            650635
            Hi,

            The command I'm using is:

            expdp system/xxxx schemas=my_schema directory=datapump_dir dumpfile=my_schema.dmp logfile=my_schema.log

            As you can see, the logfile is not keeping up with what is actually happening, I guess it needs to generate more lines before the logfile updates. I believe it's really working on the TABLES already, not stuck with MATERIALIZED VIEWS as the log suggests:
            ;;; 
            Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 02 June, 2011 6:00:00
            
            Copyright (c) 2003, 2007, Oracle.  All rights reserved.
            ;;; 
            Connected to: Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
            Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** schemas=my_schema directory=datapump_dir dumpfile=my_schema.dmp logfile=my_schema.log 
            Estimate in progress using BLOCKS method...
            Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
            Total estimation using BLOCKS method: 165.8 GB
            Processing object type SCHEMA_EXPORT/USER
            Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
            Processing object type SCHEMA_EXPORT/ROLE_GRANT
            Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
            Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
            Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
            Processing object type SCHEMA_EXPORT/LIBRARY/LIBRARY
            Processing object type SCHEMA_EXPORT/LIBRARY/GRANT/OWNER_GRANT/OBJECT_GRANT
            Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
            Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
            Processing object type SCHEMA_EXPORT/TABLE/TABLE
            Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
            Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
            Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
            Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
            Processing object type SCHEMA_EXPORT/TABLE/COMMENT
            Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
            Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
            Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
            Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
            Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
            Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
            Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
            Processing object type SCHEMA_EXPORT/VIEW/VIEW
            Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
            Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
            Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
            Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
            Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
            Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
            Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
            Cheers,

            Ados
            • 3. Re: expdp taking long time on one table
              Karan Kukreja
              HI Ados ,


              Is it still running or done ?

              Any updates on this ?


              Regads
              Kk
              • 4. Re: expdp taking long time on one table
                PrafullaNath
                It is still running , once it is done you can see SYSTEM"."SYS_EXPORT_SCHEMA_01 is successfully exported in the end of your log file.
                • 5. Re: expdp taking long time on one table
                  650635
                  Hi,

                  Thanks for replying. Yes, it was still running, very slowly. I had to kill it though as I couldn't leave it running over the weekend.

                  I'm still none the wiser as to why it is going so slowly with this table.

                  Regards,

                  Ados
                  • 6. Re: expdp taking long time on one table
                    ADaniel
                    Once I had a 40gb table. The table was pretty dynamic. Bulk data was inserted and deleted from the table (different data). So the table was actualy 8gb large, but since there was no shrink, the size used by the table was 40gb.

                    Maybe it's the same situation. Even if you only have 30 millions rows, check dba_segment and see how big this table is. Export will not export empty blocks, so the export file should not be large, but inside the database the table can be huge.

                    Also, in which tablespace does this table reside? On windows there can be issues with datafile fragmentation. Check also this aspect.
                    1 person found this helpful
                    • 7. Re: expdp taking long time on one table
                      650635
                      Hi,

                      Thanks for the reply.

                      My problem occurs on several tables, and I think it's just down to fragmentation. I've been able to rebuild some of the tables (CTAS, rename) and they datapump out in minutes. Unfortunately I'm still left with the one 25Gb one which I cannot rebuild at present. Luckily, using good old fashioned exp I can get a dump of the table in a reasonable time.
                      Also, in which tablespace does this table reside? On windows there can be issues with datafile fragmentation. Check also this aspect.
                      That looks interesting, any references/documents that discuss this issue?

                      Cheers,

                      Ados