27 December, 2021

DataPump Export using SQL Developer

 I have published a video demonstration of using SQL Developer to run a DataPump export.

Although I did not choose the "Export read-consistent view of data" (at 04:00 in the video), I strongly recommend using this option when exporting multiple tables and/or multiple schemas in a database with concurrent transactions while the export is running.

For example :

Transaction T1 might Insert into Table Y and then Delete from Table X. If DataPump does an export of Table X before the Delete and/or an export of Table Y after the Insert, the exported data would be inconsistent as far as these two tables are concerned.

Transaction T1 might Update Table G Some minutes later, Transaction T2 might Insert/Update/Delete data in Table H based on the (committed) rows in Table G. If DataPump exports Tables G and H before the DML on Table H (and after the Update at Table G), you would get inconsistent data in the export dump because the data in the dump would reflect two different points in time and not be read-consistent.

This can also happen with Materialized View Refresh's.

No comments: