This discussion is archived
7 Replies Latest reply: Jan 17, 2013 5:31 AM by Dean Gagne RSS

Datapump IMPDP hangs at "Alter Function" Step

985089 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points