FLASHBACK_TIME parameter in Data pump

This parameter is used to take the export of an object for a specified period of time.

DEMO:

Here i m creating a table called "tes" in scott schema and inserting some values at a specific timestamp.

SQL> show user

USER is "SCOTT"

SQL> create table tes (id number);

Table created.

SQL> insert into tes values(3);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

Note down the timestamp at which the table contains all the data lets say it was 

21-09-2011 15:41:00

Now, at timestamp 21-09-2011 15:43:00 deletes all rows from it.

SQL> delete from tes;

6 rows deleted.

SQL> select * from tes;

no rows selected

SQL> host expdp system/sys@delhi schemas=scott dumpfile=my.dmp FLASHBACK_TIME=\"TO_TIMESTAMP('21-09-2011 15:41:00', 'DD-MM-YYYY HH24:MI:SS')\"

Export: Release 11.2.0.1.0 - Production on Wed Sep 21 15:44:49 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_03":  system/********@delhi schemas=scott dumpfile=my.dmp FLASHBACK_TIME="TO_TIMESTAMP('21-09-2011 15:41:00', 'DD-MM-YYYY HH24:M

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 0 KB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

. . exported "SCOTT"."BONUS"                                 0 KB       0 rows

. . exported "SCOTT"."DEPT"                              5.937 KB       4 rows

. . exported "SCOTT"."EMP"                               8.570 KB      14 rows

. . exported "SCOTT"."SALGRADE"                          5.867 KB       5 rows

. . exported "SCOTT"."TES"                               5.046 KB       6 rows

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_03" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_03 is:

  D:\APP\BISHWANATH\ADMIN\DELHI\DPDUMP\MY.DMP

Job "SYSTEM"."SYS_EXPORT_SCHEMA_03" successfully completed at 15:45:14

As you see table "tes" is exported with all rows intact at timestamp 21-09-2011 15:41:00.

Now, just take the normal export without flashback_time and you will see 0 rows will be exported this time.

SQL> host expdp system/sys@delhi schemas=scott dumpfile=my1.dmp

Export: Release 11.2.0.1.0 - Production on Wed Sep 21 15:45:56 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_03":  system/********@delhi schemas=scott dumpfile=my1.dmp

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 192 KB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

. . exported "SCOTT"."DEPT"                              5.937 KB       4 rows

. . exported "SCOTT"."EMP"                               8.570 KB      14 rows

. . exported "SCOTT"."SALGRADE"                          5.867 KB       5 rows

. . exported "SCOTT"."BONUS"                                 0 KB       0 rows

. . exported "SCOTT"."TES"                                   0 KB       0 rows

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_03" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_03 is:

  D:\APP\BISHWANATH\ADMIN\DELHI\DPDUMP\MY1.DMP

Job "SYSTEM"."SYS_EXPORT_SCHEMA_03" successfully completed at 15:46:22

Enjoy:-)