Tuesday, February 03, 2009

A gotcha of Data Pump export

Today while I am answering one of OTN forum question about consistency of data pump export. I found I was under false impression that Data Pump by default guarantee dump file data consistency among all the tables. Because the Oracle Data Pump document was stating under the section named "How Data Pump Export Parameters Map to Those of the Original Export Utility" ,
"A parameter comparable to CONSISTENT is not needed"

This statement is kinda misleading, it's easy to give you false impression that Data Pump will guarentee consistency so that CONSISTENT is not needed.
Oracle now revised the document to
"A parameter comparable to CONSISTENT is not needed. Use FLASHBACK_SCN and FLASHBACK_TIME for this functionality."
which is a little better.

To get current SCN use.
select dbms_flashback.get_system_change_number from dual;

To make things worse some expdp has this header imbeded in their output message, which is even more misleading.

Export: Release - 64bit Production on Friday, 05 September, 2008 13:59:59 Copyright (c) 2003, 2005, Oracle. All rights reserved. ;;; Connected to: Oracle Database 10g Enterprise Edition Release - 64bit Production With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

FLASHBACK automatically enabled to preserve database integrity. Starting

"SYSTEM"."SYS_EXPORT_SCHEMA_02": system/******** directory=flash dumpfile=usr001_1.dmp logfile=exp_usr001_1.log

So that you know that Oracle Data Pump, up until version, doesn't guarantee data consistency among tables in a dump file. It's only guarantee point-in-time consistency of the table being exported.

also reference

Expdp Message "FLASHBACK automatically enabled" Does Not Guarantee Export Consistency
Doc ID: