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

    expdp taking long time on one table

    650635
    650635 Jun 2, 2011 12:04 PM
      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
      • 2979 Views
      • Tags:
        • 1. Re: expdp taking long time on one table
          Karan Kukreja
          Karan Kukreja Jun 2, 2011 12:34 PM (in response to 650635)
          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
            650635 Jun 2, 2011 12:48 PM (in response to Karan Kukreja)
            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
              Karan Kukreja Jun 3, 2011 5:09 AM (in response to 650635)
              HI Ados ,


              Is it still running or done ?

              Any updates on this ?


              Regads
              Kk
              • 4. Re: expdp taking long time on one table
                PrafullaNath
                PrafullaNath Jun 3, 2011 7:11 AM (in response to 650635)
                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
                  650635 Jun 6, 2011 8:07 AM (in response to PrafullaNath)
                  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
                    ADaniel Aug 11, 2019 7:22 PM (in response to 650635)
                    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
                      650635 Jun 10, 2011 3:12 PM (in response to ADaniel)
                      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