7 Replies Latest reply: Jan 17, 2013 7:31 AM by Dean Gagne-Oracle RSS

    Datapump IMPDP hangs at "Alter Function" Step

    985089
      impdp system/********@string directory=export dumpfile=P_EXP.DMP logfile= imp_p.log schemas=A,B,C,D.

      This command just hung for hours and hours ultimately I had to kill it. Did anybody has this issue before? Please help. We are on version 10.2.0.4 on Windows 2003
        • 1. Re: Datapump IMPDP hangs at "Alter Function" Step
          asahide
          Hi,

          Could you put "imp_p.log"?

          And check these views.
          dba_datapump_jobs, dba_datapump_sessions

          Regards,
          • 2. Re: Datapump IMPDP hangs at "Alter Function" Step
            TSharma-Oracle
            spool invalidobj_and_registry.txt

            SELECT SUBSTR(comp_id,1,15) comp_id, status, SUBSTR(version,1,10)
            version, SUBSTR(comp_name,1,30) comp_name
            FROM dba_registry
            ORDER BY 1;
            SELECT status, object_id, object_type, owner||'.'||object_name "OWNER.OBJECT"
            FROM dba_objects
            WHERE status != 'VALID'
            ORDER BY 4,2;
            spool.off

            Gather the statistics for all fixed objects (dynamic performance tables) in the data dictionary:

            $ sqlplus "/as sysdba"
            sql>EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
            sql>EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

            As ssoon as Import started, run this


            elect to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid, s.status, s.username, d.job_name, p.spid, s.serial#, p.pid
            from v$session s, v$process p, dba_datapump_sessions d
            where p.addr=s.paddr and s.saddr=d.saddr;

            -- Get the sid and serial# for DM00 and DW01 and execute:

            exec DBMS_SYSTEM.SET_EV([SID],[SERIAL#],10046,12 ,'');

            Run this import for quiet sometime and kill it. and than check the logs in user dump location. You can also upload the logs here.
            • 3. Re: Datapump IMPDP hangs at "Alter Function" Step
              985089
              Snippet of a trace file:
              KUPW: : in procedure RECREATE_DDL
              KUPW: : 1: Object created
              KUPW: : 1: In function CONNECT_USER with connect type SOFT, current user is: PHXADM new user is: SYSTEM
              KUPW: : 1: SOFTconnected to SYSTEM
              KUPW: : 1: in procedure BUILD_OBJECT_STRINGS
              KUPW: : 1: Process order is: 8182 and order number is: 1
              KUPW: : 1: DBMS_LOB.COPY called
              KUPW: : 1: DBMS_LOB.COPY returned
              KUPW: : 1: in procedure PUT_DDL with ALTER_FUNCTION:"PHXADM"."GETZIPPLUS4"
              KUPW: : 1: In function CONNECT_USER with connect type SOFT, current user is: SYSTEM new user is: PHXADM
              KUPW: : 1: SOFTconnected to PHXADM
              KUPW: : 1: in procedure RECREATE_DDL
              • 4. Re: Datapump IMPDP hangs at "Alter Function" Step
                TSharma-Oracle
                Do the import with EXCLUDE=ALTER_FUNCTION parameter will skip the compiling phase for functions at import. This can be done after import with utlrp.sql or manually. Let me know if this works. This should work.
                • 5. Re: Datapump IMPDP hangs at "Alter Function" Step
                  TSharma-Oracle
                  one can see from the trace file that the hang is occurring when compiling "PHXADM"."GETZIPPLUS4" function.
                  • 6. Re: Datapump IMPDP hangs at "Alter Function" Step
                    985089
                    Awesome ..Import done in 7 minutes. Awesome. I just compiled that function separately.
                    Thanks...
                    • 7. Re: Datapump IMPDP hangs at "Alter Function" Step
                      Dean Gagne-Oracle
                      I would probably add one more step. There could be other things the alter is doing. Like setting up debug paramtes, etc. It would be best to run the import with a sqlfile and the include of the alter_function. Then you can look at the sqlfile to see if there are things (other than recompiles) that are being done. If so, you can do that before running utlrp.

                      Hope this helps

                      Dean