datapump reterive original data using flashback scn
Retrieve original data:-
It may be useful to export the image of a table the way it existed before a change was committed.if the database is properly configured, the database flashback query facility also integrated with expdp may be used.
SQL> conn / as sysdba
Connected.
SQL> create restore point original_emp;
Restore point created.
SQL> select scn,name from v$restore_point;
SCN
———-
NAME
——————————————————————————–
518527
ORIGINAL_EMP
SQL> conn scott/tiger;
Connected.
SQL> select sum(sal) from emp;
SUM(SAL)
———-
29025
SQL> update test set sal=sal*1.1;
14 rows updated.
Here Test table is copy of emp table.
SQL> commit;
Commit complete.
SQL> select sum(sal) from test;
SUM(SAL)
———-
31927.5
[oracle@localhost rlwrap-0.30]$ expdp scott/tiger dumpfile=original directory=test_dir tables=test flashback_scn=518527
Export: Release 10.2.0.1.0 – Production on Friday, 07 May, 2010 13:33:02
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 “SCOTT”.”SYS_EXPORT_TABLE_01″: scott/******** dumpfile=original directory=test_dir tables=test flashback_scn=518527
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
. . exported “SCOTT”.”TEST” 7.820 KB 14 rows
Master table “SCOTT”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/home/oracle/datapumptest/original.dmp
Job “SCOTT”.”SYS_EXPORT_TABLE_01″ successfully completed at 13:33:11
SQL> drop table test;
Table dropped.
SQL> commit;
Commit complete.
[oracle@localhost rlwrap-0.30]$ impdp scott/tiger dumpfile=original directory=test_dir tables=test
Import: Release 10.2.0.1.0 – Production on Friday, 07 May, 2010 13:34:57
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 “SCOTT”.”SYS_IMPORT_TABLE_01″ successfully loaded/unloaded
Starting “SCOTT”.”SYS_IMPORT_TABLE_01″: scott/******** dumpfile=original directory=test_dir tables=test
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “SCOTT”.”TEST” 7.820 KB 14 rows
Job “SCOTT”.”SYS_IMPORT_TABLE_01″ successfully completed at 13:35:00
[oracle@localhost rlwrap-0.30]$
SQL> select sum(sal) from test;
SUM(SAL)
———-
29025
DBA 