Data Pump: Consistent Export?
- by Mike Dietrich
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=SYSTIMESTAMPin 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
