Tracing datapump sessions

Tracing datapump is not straight forward, you can trace the datapump worker(dw00) or datapump master(DM00) with 10046 to get the sql etc or use the TRACE option in expdp/impdp

Where in expdp has an option called trace where you can trace the session associated to datapump and then see what its waiting for or running (internal sql statements)

There are number of bugs for datapump, tracing may help to determine what we are encountering

To begin with, we need to know different levels of tracing in datapump, the following table depicts the same.

 Trace   DM   DW  ORA  Lines  
 level  trc  trc  trc     in  
 (hex) file file file  trace   Purpose
 ------- ---- ---- ---- ------ -----------------------------------------------  
   10300    x    x    x  SHDW: To trace the Shadow process (API) (expdp/impdp)  
   20300    x    x    x  KUPV: To trace Fixed table  
   40300    x    x    x  'div' To trace Process services  
   80300    x            KUPM: To trace Master Control Process (MCP)      (DM) 
  100300    x    x       KUPF: To trace File Manager 
  200300    x    x    x  KUPC: To trace Queue services 
  400300         x       KUPW: To trace Worker process(es)                (DW) 
  800300         x       KUPD: To trace Data Package
 1000300         x       META: To trace Metadata Package
 --- +
 1FF0300    x    x    x  'all' To trace all components          (full tracing)

In case of full tracing two trace files are generated in BACKGROUND_DUMP_DEST just like standard tracing. And one trace file is generated in USER_DUMP_DEST.

Shadow Process trace file: _ora_.trc

Individual tracing level values in hexadecimal are shown except last one in the list. You can use individual value or combination of values. If you sum all the individual values you will get 1FF0300 which is full tracing.

To use full level tracing issue data pump export as,
expdp DUMPFILE=expdp.dmp LOGFILE=expdp.log TRACE=1FF0300

To use full level tracing for data pump import operation issue import as,
impdp DUMPFILE=expdp.dmp LOGFILE=expdp.log TRACE=1FF0300

However for most cases full level tracing is not required. 
As trace 400300 is to trace Worker process(es) and trace 80300 is to trace 
Master Control Process (MCP). So combining them is trace 480300 
and by using trace 480300 you will be able to trace both Master Control process 
(MCP) and the Worker process(es). This would serve the purpose.

So to diagnose any data pump export problem issue,
expdp DUMPFILE=expdp.dmp LOGFILE=expdp.log TRACE=480300

To diagnose any data pump import problem issue,
impdp DUMPFILE=expdp.dmp LOGFILE=expdp.log TRACE=480300

In case of standard tracing trace files are generated in BACKGROUND_DUMP_DEST. In case of standard tracing,

– If it is Master Process trace file then generated file name is, _dm_.trc

– If it is Worker Process trace file then generated file name is,_dw_.trc