Difference between revisions of "Datapump"

Revision as of 13:11, 9 December 2011

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

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