基于flashback_scn的expdp導出方法是什么

本篇內容介紹了“基于flashback_scn的expdp導出方法是什么”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

在使用10g后的Oracle data pump導出數(shù)據(jù)時，我們可以使用flashback_scn參數(shù)指定導出的時間點，這時

oracle會使用flashback query查詢導出scn時的數(shù)據(jù)，flashback query使用undo，無需打開flashback database功能。

也就是說，只要undo信息不被覆蓋，即使數(shù)據(jù)庫被重啟，仍然可以進行基于flashback_scn的導出動作。

--以scott用戶做測試

oracle@wang:/home/oracle$sqlplus scott/tiger;

SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 15 07:43:24 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

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

SQL> show user;

USER is "SCOTT"

SQL>  select current_scn from v$database;

CURRENT_SCN

-----------

   21870773                           (記為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

-----------

   21870796                            (記為2號時間點)

SQL> insert into t values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

   21870805

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  835104768 bytes

Fixed Size                  2257840 bytes

Variable Size             549456976 bytes

Database Buffers          281018368 bytes

Redo Buffers                2371584 bytes

Database mounted.

Database opened.

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

   21871307                      (記為3號時間點)

SQL> conn scott/tiger;

Connected.

SQL> insert into t values(3);

1 row created.

SQL> commit;

Commit complete.

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

   21871340                    (記為4號時間點)

SQL> select * from t;

       NUM

----------

         1

         2

         3

--現(xiàn)在開始做expdp導出

(1號時間點)

 expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=t1.dmp tables=t flashback_scn=21870773

(2號時間點) 

 expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=t2.dmp tables=t flashback_scn=21870796

(3號時間點) 

 expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=t3.dmp tables=t flashback_scn=21871307

(4號時間點)

 expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=t4.dmp tables=t flashback_scn=21871340

oracle@wang:/home/oracle$ expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=t1.dmp tables=t 

flashback_scn=21870773

Export: Release 11.2.0.4.0 - Production on Fri Mar 15 07:52:18 2019

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

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

Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=DATA_PUMP_DIR dumpfile=t1.dmp tables=t flashback_scn=21870773 

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"."T"                                 4.984 KB       0 rows

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

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

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

  /u01/app/oracle/admin/DBdb/dpdump/t1.dmp

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Fri Mar 15 07:52:28 2019 elapsed 0 00:00:08

oracle@wang:/home/oracle$ expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=t2.dmp tables=t 

flashback_scn=21870796

Export: Release 11.2.0.4.0 - Production on Fri Mar 15 07:52:34 2019

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

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

Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=DATA_PUMP_DIR dumpfile=t2.dmp tables=t flashback_scn=21870796 

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

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

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

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

  /u01/app/oracle/admin/DBdb/dpdump/t2.dmp

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Fri Mar 15 07:52:44 2019 elapsed 0 00:00:07

oracle@wang:/home/oracle$ expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=t3.dmp tables=t 

flashback_scn=21871307

Export: Release 11.2.0.4.0 - Production on Fri Mar 15 07:52:54 2019

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

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

Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=DATA_PUMP_DIR dumpfile=t3.dmp tables=t flashback_scn=21871307 

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

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

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

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

  /u01/app/oracle/admin/DBdb/dpdump/t3.dmp

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Fri Mar 15 07:53:03 2019 elapsed 0 00:00:07

oracle@wang:/home/oracle$ expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=t4.dmp tables=t 

flashback_scn=21871340

Export: Release 11.2.0.4.0 - Production on Fri Mar 15 07:53:12 2019

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

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

Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=DATA_PUMP_DIR dumpfile=t4.dmp tables=t flashback_scn=21871340 

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

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

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

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

  /u01/app/oracle/admin/DBdb/dpdump/t4.dmp

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Fri Mar 15 07:53:22 2019 elapsed 0 00:00:07

oracle@wang:/home/oracle$

--現(xiàn)在開始做impdp導出

(1號時間點)

 drop table t purge;

 impdp scott/tiger directory=DATA_PUMP_DIR dumpfile=t1.dmp  

(2號時間點) 

 drop table t purge;

 impdp scott/tiger directory=DATA_PUMP_DIR dumpfile=t2.dmp  

(3號時間點) 

 drop table t purge;

 impdp scott/tiger directory=DATA_PUMP_DIR dumpfile=t3.dmp  

(4號時間點)

 drop table t purge;

 impdp scott/tiger directory=DATA_PUMP_DIR dumpfile=t4.dmp  

“基于flashback_scn的expdp導出方法是什么”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關的知識可以關注億速云網(wǎng)站，小編將為大家輸出更多高質量的實用文章！