Well it is not needed anymore according to Oracle. Let’s take a look at a bit of history. From 9i, when flashback database came in, it was possible to export using a SCN number by setting the parameter flashback_scn or flashback_time. Below you can see the relevant options from a 10g exp help=y command
CONSISTENT cross-table consistency(N)
FLASHBACK_SCN SCN used to set session snapshot back to
FLASHBACK_TIME time used to get the SCN closest to the specified time
So both consistent=y and flashback options were available (provided you were using undo management).
Roll forward to 10g and onwards (and bear in mind that exp/imp are rumoured to be unsupported with 11g R2) and it appears obvious that using flashback is the way to go to get a consistent query/export using Datapump
Flashback_scn
There are at least 3 ways of getting the latest scn from a database
select current_scn from v$database;
select dbms_flashback.get_system_change_number from dual;
using oradebug on kcsgscn_ (for full details see http://www.eygle.com/archives/2006/01/oracle_howto_get_scn.html)
So if we want to run a large export whilst the database is in use then ideally we should always use one of the two flashback parameters, preferably called from a parameter file (my personal preference)
Flashback_time
There seems to be a few notes around on issues about the usage of this parameter and it seems to be tricky to get the parameter right first time
userid=system schemas=system dumpfile=test.dmp FLASHBACK_TIME=”to_timestamp(’05-DEC-2008 14:13:00′,’DD-MON-YYYY HH24:MI:SS’)”
There is an interesting note http://forums.oracle.com/forums/thread.jspa?threadID=623510 that talks about export consistency when using flashback_time
Here is an worked example of using flashback_time to copy a table as at a point in the past.
create table system.test as select * from dba_objects where rownum <101;
Table created.
SQL> select count(*) from test;
100
Export the table (100 rows) – noting the time.
expdp parfile=parfile.dat (userid=system/xxxx tables=system.test dumpfile=test.dmp)
Export: Release 11.1.0.7.0 – 64bit Production on Friday, 05 December, 2008 19:35:18
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SYSTEM”.”SYS_EXPORT_TABLE_01″: system/******** parfile=parfile.dat
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 “SYSTEM”.”TEST” 18.58 KB 100 rows
Master table “SYSTEM”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/app/oracle/admin/MDWDEV2A/dpdump/test.dmp
Job “SYSTEM”.”SYS_EXPORT_TABLE_01″ successfully completed at 19:35:37
Delete some rows
SQL> delete from system.test where rownum < 50;
49 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from system.test;
51
Now try the export again, using a flasback_time before the last export
$ rm test.dmp
$ expdp parfile=parfile.dat (userid=system tables=system.test dumpfile=test.dmp FLASHBACK_TIME=”to_timestamp(‘05-DEC-2008 19:35:00‘,’DD-MON-YYYY HH24:MI:SS’)”
Export: Release 11.1.0.7.0 – 64bit Production on Friday, 05 December, 2008 19:38:30
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SYSTEM”.”SYS_EXPORT_TABLE_01″: system/******** parfile=parfile.dat
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 “SYSTEM”.”TEST” 18.58 KB 100 rows
Master table “SYSTEM”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/app/oracle/admin/MDWDEV2A/dpdump/test.dmp
There are a lot of permutations and possibilities using the flashback technology and combining it with Datapump and I have only explored a small area here
