Datapump

Use the flashback parameter to export and import a consistent dump

expdp <user>/<pwd> parfile=db_params.par

where db_params.par would look like this:

flashback_time=”to_timestamp (to_char (sysdate, ‘YYYY-MM-DD HH24:MI:SS’), ‘YYYY-MM-DD HH24:MI:SS’)”
directory=DATAPUMP_DIR
dumpfile=db_schema.dmp
schemas=(apps,test)

This performs a consistent dump of 2 schemas

Keeping the parameters in a file like this allows you to reuse it for the import:

impdp <user>/<pwd> parfile=db_params.par

Monitoring Datapump sessions

select s.sid
,      s.serial#
from   v$session s
,      dba_datapump_sessions dps
where s.saddr = dps.saddr;

Once the session is identified, you can see the parallel query slave sessions spawned off by the main DataPump session by querying the V$PX_SESSION view:

select sid from v$px_session
where qcsid = <DP Session ID>;

How long will it take to export?

DataPump sends information about its progress to the longops view and can be consulted thus: select sid , serial# , sofar , totalwork from v$session_longops where 1=1 and opname = '<DP Job Name>' and sofar != totalwork /

Enable trace mode

Two trace possibilities are available, standard and full:

impdp trace=0480300 ...

provides standard tracing. Trace files will be in user-dump_dest

impdp trace=1FF0300 ...

provides full tracing. Trace files will be in user-dump_dest

Enabling SQL trace

Cannot simply use:

alter session set sql_trace=true;

as DataPump starts another session. An event has to be set:

select sid
,      serial#
,      username
,      program
from   v$session 
where  1=1
and    upper(program) like '%(DW%)'
or     upper(program) like '%(DM%)'
/

then use the sid and serial# to set the event:

exec dbms_system.set_ev(<sid>, <serial#>, 10046, 12, );

Trace files will be in user-dump_dest