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
DELETEson that table as inconsistent
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:
in your parameter file. Then it will be consistent according to the timestamp when the process has been started. You could use
FLASHBACK_SCNinstead and determine the
SCNbeforehand if you’d like to be exact.
So sorry if I had proclaimed a feature which unfortunately is not there by default 🙁