DataPump Export (EXPDP) With FLASHBACK_SCN Produces Wrong Result (Doc ID 1389779.1)

Last updated on JANUARY 23, 2012

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.4 to 10.2.0.5 - Release: 10.2 to 10.2
Information in this document applies to any platform.

Goal

In version 10g while doing expdp with FLASHBACK_SCN does not produce correct results when all rows of table are deleted and you try to export data before delete. The behavior is not reproduced when there is insert or when only few rows are deleted.

Following was tested:

1. Create a table

SQL> create table test_flash (id number, create_date date);

2. Insert a row
   
SQL> insert into test_flash values (1, sysdate);
SQL> commit;

3. Get the current SCN number

SQL> select current_scn as SCN1 from v$database;

If you perform expdp with the parameter FLASHBACK_SCN=<SCN1>, it correctly exports 1 row.

4. Delete the row inserted before

SQL> delete test_flash where id = 1;
SQL> commit;

5. Get the current SCN number

SQL> select current_scn as SCN2 from v$database;

Now, if you use flashback query with <SCN1>, it returns the row before deleted.

SQL> select * from test_flash as of scn &scn1;

But if you perform expdp with the parameter FLASHBACK_SCN=<SCN1>, it exports 0 rows instead of 1.

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms