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
SELECT SUBSTR(comp_id,1,15) comp_id, status, SUBSTR(version,1,10)
version, SUBSTR(comp_name,1,30) comp_name
ORDER BY 1;
SELECT status, object_id, object_type, owner||'.'||object_name "OWNER.OBJECT"
WHERE status != 'VALID'
ORDER BY 4,2;
Gather the statistics for all fixed objects (dynamic performance tables) in the data dictionary:
$ sqlplus "/as sysdba"
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.
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
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.
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