在使用10g後的Oracle data pump匯出資料時，我們可以使用flashback_scn引數指定匯出的時間點，這時oracle會使用flashback query查詢匯出scn時的資料，flashback query使用undo，無需開啟flashback database功能。也就是說，只要undo資訊不被覆蓋，即使資料庫被重啟，仍然可以進行基於flashback_scn的匯出動作。

來做一個簡單的實驗：

oracle@ibmvs_a@/oracle $ sqlplus test/test

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Jun 27 21:34:55 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

11850458 (記為1號時間點)

SQL> create table t (num number);

Table created.

SQL> insert into t values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

11850483 (記為2號時間點)

SQL> insert into t values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

11850489

SQL> conn / as sysdba

Connected.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1124073472 bytes

Fixed Size 2078688 bytes

Variable Size 591398944 bytes

Database Buffers 524288000 bytes

Redo Buffers 6307840 bytes

Database mounted.

Database opened.

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

11850721 (記為3號時間點)

SQL> conn test/test

Connected.

SQL> insert into t values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

11850893 (記為4號時間點)

好，現在開始做expdp匯出。

oracle@ibmvs_a@/other/dumpdir $ export ORACLE_SID=HT

oracle@ibmvs_a@/other/dumpdir $ expdp test/test directory=dumpdir dumpfile=t.dmp tables=t flashback_scn=11850458 (1號時間點)

Export: Release 10.2.0.3.0 - 64bit Production on Monday, 27 June, 2011 21:39:02

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** directory=dumpdir dumpfile=t.dmp tables=t flashback_scn=11850458

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

ORA-31693: Table data object "TEST"."T" failed to load/unload and is being skipped due to error:

ORA-02354: error in exporting/importing data

ORA-01466: unable to read data - table definition has changed

Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

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

Dump file set for TEST.SYS_EXPORT_TABLE_01 is:

/other/dumpdir/t.dmp

Job "TEST"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 21:39:38

oracle@ibmvs_a@/other/dumpdir $ expdp test/test directory=dumpdir dumpfile=t.dmp tables=t flashback_scn=11850483 (2號時間點)

Export: Release 10.2.0.3.0 - 64bit Production on Monday, 27 June, 2011 21:40:19

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** directory=dumpdir dumpfile=t.dmp tables=t flashback_scn=11850483

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 "TEST"."T" 4.906 KB 1 rows

Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

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

Dump file set for TEST.SYS_EXPORT_TABLE_01 is:

/other/dumpdir/t.dmp

Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 21:40:30

oracle@ibmvs_a@/other/dumpdir $ expdp test/test directory=dumpdir dumpfile=t.dmp tables=t flashback_scn=11850721 (3號時間點)

Export: Release 10.2.0.3.0 - 64bit Production on Monday, 27 June, 2011 21:41:09

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** directory=dumpdir dumpfile=t.dmp tables=t flashback_scn=11850721

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 "TEST"."T" 4.914 KB 2 rows

Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

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

Dump file set for TEST.SYS_EXPORT_TABLE_01 is:

/other/dumpdir/t.dmp

Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 21:41:23

oracle@ibmvs_a@/other/dumpdir $ expdp test/test directory=dumpdir dumpfile=t.dmp tables=t flashback_scn=11850893 (4號時間點)

Export: Release 10.2.0.3.0 - 64bit Production on Monday, 27 June, 2011 21:41:47

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** directory=dumpdir dumpfile=t.dmp tables=t flashback_scn=11850893

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 "TEST"."T" 4.921 KB 3 rows

Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

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

Dump file set for TEST.SYS_EXPORT_TABLE_01 is:

/other/dumpdir/t.dmp

Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 21:42:01

基於flashback_scn的expdp匯出

相關文章