Data Pump: Consistent Export?

Ouch … I have to admit as I did say in several workshops in the past weeks that a data pump export with expdp is per se consistent.

Well … I thought it is … but it’s not. Thanks to a customer who is doing a large unicode migration at the moment. We were discussing parameters in the expdp’s par file. And I did ask my colleagues after doing some research on MOS. And here are the results of my “research”:

• MOS Note 377218.1 has a nice example showing a data pump export of a partitioned table with DELETEs on that table as inconsistent
• Background:
  Back in the old 9i days when Data Pump was designed flashback technology wasn’t as popular and well known as today – and UNDO usage was the major concern as a consistent per default export would have heavily relied on UNDO. That’s why – similar to good ol’ exp – the export won’t operate per default in consistency mode
• To get a consistent data pump export with expdp you’ll have to set:
  FLASHBACK_TIME=SYSTIMESTAMP
  in your parameter file. Then it will be consistent according to the timestamp when the process has been started. You could use FLASHBACK_SCN instead and determine the SCN beforehand if you’d like to be exact.

So sorry if I had proclaimed a feature which unfortunately is not there by default 🙁

– Mike