Export data to a previous time or point-in-time

From oracle 9i and 10g along with export/import utility the two parameters named FLASHBACK_SCN and FLASHBACK_TIME really help us in order to take dump up to a previous point. This allows use for example, to export the original data of a table, in which rows have been updated erroneously, or rows were deleted by mistake.

In order to illustrate the usage of these two parameters I used an example. In the example I used a table named TEST_NUM and owner of the table is ARJU whose password is A.

1)Determine the current SCN of the database.

SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1252872

2)Note the contents of table test_num.
SQL> SELECT * FROM TEST_NUM;
A
----------
100
100
5
7

3)Update the table test_num.
SQL> UPDATE TEST_NUM SET A=A+1;
4 rows updated.

SQL> COMMIT;
Commit complete.

SQL> SELECT * FROM TEST_NUM;
A
----------
101
101
6
8
4)Export the table test_num with parameter FLASHBACK_SCN to 1252872.
So export operation will be done up to SCN 1252872. Changes after SCN 1252872 will not reflected with dump file.

SQL> HOST expdp ARJU/A TABLES=TEST_NUM FLASHBACK_SCN=1252872
Export: Release 10.2.0.1.0 - Production on Friday, 16 May, 2008 2:50:09
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "ARJU"."SYS_EXPORT_TABLE_01": ARJU/******** TABLES=TEST_NUM FLASHBACK_SCN=1252872
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "ARJU"."TEST_NUM" 4.937 KB 4 rows
Master table "ARJU"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ARJU.SYS_EXPORT_TABLE_01 is:
/oracle/app/oracle/product/10.2.0/db_1/admin/dbase/dpdump/expdat.dmp
Job "ARJU"."SYS_EXPORT_TABLE_01" successfully completed at 02:50:17


Alternatively you can used FLASHBACK_TIME parameter like on unix,
flashback_time=\"TO_TIMESTAMP\(\'16-05-2008 11:21:42\', \'DD-MM-YYYY HH24:MI:SS\'\)\"

On windows,
flashback_time=\"TO_TIMESTAMP('16-05-2008 13:24:26', 'DD-MM-YYYY HH24:MI:SS')\"

Use backslash(\) before special character.

5)Drop the table and Import the dump file.

Here I provide no location so default location is used. While dump as I did not provide any DIRECTORY or DUMPFILE parameter and hence it looked for default directory DATA_PUMP_DIR and by default location of this parameter is $ORACLE_HOME/admin/$ORACLE_SID/dpdump/ (/oracle/app/oracle/product/10.2.0/db_1/admin/dbase/dpdump/) and searched for default dump file which is expdat.dmp.

SQL> DROP TABLE TEST_NUM;
Table dropped.

SQL> HOST impdp ARJU/A
Import: Release 10.2.0.1.0 - Production on Friday, 16 May, 2008 2:51:15

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "ARJU"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "ARJU"."SYS_IMPORT_FULL_01": ARJU/********
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ARJU"."TEST_NUM" 4.937 KB 4 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ARJU"."SYS_IMPORT_FULL_01" successfully completed at 02:51:20

6)Now look at the data in TEST_NUM.

SQL> SELECT * FROM TEST_NUM;
A
----------
100
100
5
7