11 Replies Latest reply: Aug 15, 2013 12:15 PM by TnJed RSS

    Expdp full=y hang



      I'm using Oracle on Linux 6.


      i'm doing full database export

      Expdp system/<system pass> directory=xxx.dmp dumpfile=aaa.dmp logfile=xxx.log FULL=Y

      : :: : : :: : : : ;

      Estimate in progress using BLOCKS method...

      Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

      Total estimation using BLOCKS method: 24.87 MB

      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/DB_LINK



      then my export hangs..... Sad

      checked in alert log nothing found.


      and then killed the job and reran again but same....checked the status and it's saying EXECUTING, dont know where i'm doing wrong, can anyone please help me out


      thanks in advance

        • 1. Re: Expdp full=y hang
          Lubiez Jean-Valentin



          You may add the Parameter STATUS=15.


          Then, you'll have a feedback on you screen every 15 seconds, and see better what the DATAPUMP Job is doing.



          Hope this help.

          Best regards,

          Jean-Valentin Lubiez

          • 2. Re: Expdp full=y hang
            Asif Muhammad



            Can you confirm whether you are running the expdp command from Server end or Client level end. I remember an instance where the DB server was 11g and my client was running on 10g and it hung too, if this is the same with you try to expdp from Server end.


            Also please refer following note and check if it helps you:

            Expdp Hangs During Exporting Data Due to a Lock Situation [ID 756834.1]

            Data Pump Hanging When Exporting To NFS Location [ID 434508.1]



            • 3. Re: Expdp full=y hang



              I'm running the expdp command from Server so there should no issue with version different.


              The strange thing here is that I have 2 instances DEVT and TEST where both have the same tables structure. In the TEST users are testing so they key-in a lot of data, while in the DEVT the data is static. The hanging of expdp only happens in the TEST.


              In the Doc ID 786165.1 it said for expdp the value for parameter ESTIMATE  is either BLOCKS (default) or STATISTICS, where for STATISTICS, all tables should have been analyzed recently. It means the default ESTIMATE =  BLOCKS, no need to analyze all tables before using expdp with FULL=y,


              Do I need to analyze all tables in the TEST using expdp with FULL=y and  ESTIMATE =  BLOCKS?


              Thanks in advance.

              • 4. Re: Expdp full=y hang



                What it shows when you Query



                select * from dba_datapump_sessions;

                select * from dba_datapump_jobs;

                add the parameter EXCLUDE=STATISTICS and then try



                • 5. Re: Expdp full=y hang



                  I have add the parameter EXCLUDE=STATISTICS and then try again. The expdp still hangs with:


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

                  With the Partitioning, OLAP, Data Mining and Real Application Testing options

                  Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/******** DIRECTORY=xxx_dir DUMPFILE=xxx.dmp FULL=y EXCLUDE=STATISTICS

                  Estimate in progress using BLOCKS method...

                  Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

                  Total estimation using BLOCKS method: 227.5 MB






                  and it hangs.


                  The queries:


                  select * from dba_datapump_sessions;


                  OWNER_NAME    JOB_NAME    INST_ID    SADDR    SESSION_TYPE

                  SYSTEM    SYS_EXPORT_FULL_01    1    000000015F852538    DBMS_DATAPUMP

                  SYSTEM    SYS_EXPORT_FULL_01    1    000000015F62B4D0    MASTER

                  SYSTEM    SYS_EXPORT_FULL_01    1    000000015F6F8D18    WORKER


                  select * from dba_datapump_jobs;



                  SYSTEM    SYS_EXPORT_FULL_01    EXPORT    FULL    EXECUTING    1    1    3


                  Any idea?

                  • 6. Re: Expdp full=y hang



                    is your database is AMM enable,

                    What is the value for STREAMS_POOL_SIZE,

                    Have you tried with  CONTENT=METADATA_ONLY  is completed  successfully. or try to export excluding the schemas SYS and SYSTEM.

                    you can also check the metalink id

                    DataPump Export (EXPDP) Hangs When Exporting Referential Constraints [ID 1368586.1]


                    • 7. Re: Expdp full=y hang



                      Yes, the expdp with CONTENT=METADATA_ONLY can go through without any error.


                      Here is the STREAMS_POOL_SIZE and other in my database:


                      select * from v$sgainfo;


                      NAME    BYTES    RESIZEABLE

                      Fixed SGA Size    2235208    No

                      Redo Buffers    12132352    No

                      Buffer Cache Size    1627389952    Yes

                      Shared Pool Size    2566914048    Yes

                      Large Pool Size    16777216    Yes

                      Java Pool Size    16777216    Yes

                      Streams Pool Size    33554432    Yes

                      Shared IO Pool Size    33554432    Yes

                      Granule Size    16777216    No

                      Maximum SGA Size    4275781632    No

                      Startup overhead in Shared Pool    125798072    No

                      Free SGA Memory Available    0   


                      Any idea how to fix this?

                      • 8. Re: Expdp full=y hang



                        have you tried the metalink solution

                        Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT

                        DataPump Export (EXPDP) Hangs When Exporting Referential Constraints [ID 1368586.1]

                        you can revert it back if you want as per solution.

                        • 9. Re: Expdp full=y hang



                          I have applied the solution mentioned in the Note ID 1368586.1


                          update sys.metaview$ set properties = properties + 1024 where viewname = 'KU$_REF_CONSTRAINT_VIEW';


                          1 row updated.




                          Commit complete.


                          After the the expdp still hangs


                          Any idea?

                          • 10. Re: Expdp full=y hang
                            Lubiez Jean-Valentin




                            Do you have any Table with LONG columns ?


                            To check that, you may use the following query:


                            select owner, table_name, column_name

                            from dba_tab_cols

                            where data_type like 'LONG%';


                            If this is the case, then you may use the parameter ACCESS_METHOD as showed in the Note below:


                            DataPump Export (EXPDP) Is Hanging While Exporting A Table With LONG Column [ID 813396.1]


                            Else, what you've noticed between TEST and DEV is interesting.


                            Do you have the same memory parameters between the 2 Databases ?


                            If the users modify continuously the Data in the Database TEST then, you may use the following parameter during the Export:


                            FLASHBACK_TIME=\"to_timestamp\(to_char\(sysdate,\'yyyy-mm-dd hh24:mi:ss\'\),\'yyyy-mm-dd hh24:mi:ss\'\)\"



                            Hope this help,

                            Best Regards,

                            Jean-Valentin Lubiez


                            Message was edited by: LubiezJean-Valentin

                            • 11. Re: Expdp full=y hang

                              This is a great discussion. I'm having the same issue, a Datapump export that hangs. Mine hangs on the same table every time. I have tried the status, and the  Note ID 1368586.1 referential constraint change. None work. It's not locks, either, as I have tried it without any locks.


                              Still a great discussion. and now I get a status every 15 seconds, so that's progress. It looks like this:


                              Job: SYS_EXPORT_SCHEMA_04
                                Operation: EXPORT
                                Mode: SCHEMA
                                State: EXECUTING
                                Bytes Processed: 20,425,888
                                Percent Done: 88
                                Current Parallelism: 1
                                Job Error Count: 0
                                Dump File: /usr/local/oracle/admin/gp7/dpdump/130814acurup.dmp
                                  bytes written: 20,963,328

                              Worker 1 Status:
                                State: EXECUTING
                                Object Schema: AC_URUP
                                Object Name: AC_SCHEDULE
                                Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
                                Completed Objects: 28
                                Total Objects: 47
                                Worker Parallelism: 1


                              It hangs on the same table every time. Very strange. Keep up the discussion please.