Skip to content
Advertisements

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

Advertisements
No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: