expdp之query、flashback_scn参数的使用

实验环境
操作系统：Centos 7.1
数据库：oracle 11.2.0.4

 

在使用10g后的Oracle Data Pump导出数据时, expdp中提供了query参数，可以在需要按条件导出表中部分数据时使用，它的使用就像是在select语句中的where条件使用一样。同时也提供了flashback_scn和flashback_time参数指定导出的时间点，这时oracle会使用flashback query查询导出scn时的数据，flashback query使用undo，无需打开flashback database功能。也就是说，只要undo信息不被覆盖，即使数据库被重启，仍然可以进行基于flashback_scn和flashback_time的导出动作。

 

--创建测试表

SYS@seiang11g>create table seiang.test1 (id number,name varchar2(20)) tablespace seiang;

Table created.

 

SYS@seiang11g>create table seiang.test2 (id number,time date) tablespace seiang;

Table created.

 

SYS@seiang11g>select owner,table_name,tablespace_name from dba_tables where owner='SEIANG';

 

OWNER                          TABLE_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------ ------------------------------

SEIANG                         TEST2                          SEIANG

SEIANG                         TEST1                          SEIANG

 

 

--插入测试数据

SYS@seiang11g>insert into seiang.test1 select level,lpad(level,20,'*') from dual connect by level <= 100;

100 rows created.

 

SYS@seiang11g>commit;

Commit complete.

 

SYS@seiang11g>insert into seiang.test2 select level,sysdate-50+level from dual connect by level <= 100;

100 rows created.

 

SYS@seiang11g>commit;

Commit complete.

 

--创建目录

SYS@seiang11g>create directory dumpdir as '/u01/app/oracle/exp_imp_dump';

Directory created.

 

 

--测试使用query导出，分别使用参数文件导出和单条语句导出

1、使用参数文件导出（query条件在parfile中不需要用'\'进行转义）

[oracle@seiang11g ~]$ vim /u01/app/oracle/exp_imp_dump/parfile_expdp.par                                            

QUERY=seiang.test1:"where id<=10"

NOLOGFILE=y

DIRECTORY=dumpdir

DUMPFILE=seiang.test1_parfile.dmp

tables=seiang.test1

 

[oracle@seiang11g ~]$ expdp seiang/seiang parfile=/u01/app/oracle/exp_imp_dump/parfile_expdp.par

 

Export: Release 11.2.0.4.0 - Production on Wed Aug 16 15:39:09 2017

 

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 "SEIANG"."SYS_EXPORT_TABLE_01":  seiang/******** parfile=/u01/app/oracle/exp_imp_dump/parfile_expdp.par

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 "SEIANG"."TEST1"                            6.210 KB      30 rows

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

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

Dump file set for SEIANG.SYS_EXPORT_TABLE_01 is:

  /u01/app/oracle/exp_imp_dump/seiang.test1_parfile.dmp

Job "SEIANG"."SYS_EXPORT_TABLE_01" successfully completed at Wed Aug 16 15:39:14 2017 elapsed 0 00:00:05

 

2、单条语句进行导出

 

[oracle@seiang11g ~]$ expdp seiang/seiang directory=dumpdir dumpfile=seiang_test1.dmp tables=seiang.test1 query=seiang.test1:\"where id<=50\";

-bash: =50": No such file or directory

（特别注意：之所以出现上面的错误，是因为运算符也是需要做转义的。所以为了避免因为疏忽缺少转义而带来的错误，所有对于单条语句导出时，需要加一对单引号query=seiang.test1:'"where id <= 50 "'）

 

[oracle@seiang11g ~]$ expdp seiang/seiang directory=dumpdir dumpfile=seiang_test1.dmp tables=seiang.test1 query=seiang.test1:'"where id <= 50 "';

或者

[oracle@seiang11g ~]$ expdp seiang/seiang directory=dumpdir dumpfile=seiang_test11.dmp tables=seiang.test1 query='seiang.test1:"where id <= 50 "';

 

 

Export: Release 11.2.0.4.0 - Production on Wed Aug 16 14:37:48 2017

 

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 "SEIANG"."SYS_EXPORT_TABLE_01":  seiang/******** directory=dumpdir dumpfile=seiang_test1.dmp tables=seiang.test1 query=seiang.test1:"where id <= 50 "

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 "SEIANG"."TEST1"                            6.757 KB      50 rows

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

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

Dump file set for SEIANG.SYS_EXPORT_TABLE_01 is:

  /u01/app/oracle/exp_imp_dump/seiang_test1.dmp

Job "SEIANG"."SYS_EXPORT_TABLE_01" successfully completed at Wed Aug 16 14:38:01 2017 elapsed 0 00:00:08

 

 

--查询SCN号（第1个）

SYS@seiang11g>select dbms_flashback.get_system_change_number from dual;

 

GET_SYSTEM_CHANGE_NUMBER

------------------------

                 1676032          (记录第一个SCN)

 

--再次插入30条测试数据

SYS@seiang11g>insert into seiang.test1 select level,lpad(level,20,'#') from dual connect by level <= 30;

30 rows created.

 

SYS@seiang11g>commit;

Commit complete.

 

SYS@seiang11g>insert into seiang.test2 select level,sysdate-50+level from dual connect by level <= 30;

30 rows created.

 

SYS@seiang11g>commit;

Commit complete.

 

SYS@seiang11g>select count(*) from seiang.test1;

 

  COUNT(*)

----------

       130

 

SYS@seiang11g>select count(*) from seiang.test2;

 

  COUNT(*)

----------

       130

 

 

SYS@seiang11g>shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

 

SYS@seiang11g>startup

ORACLE instance started.

 

Total System Global Area 1252663296 bytes

Fixed Size                  2252824 bytes

Variable Size             822087656 bytes

Database Buffers          419430400 bytes

Redo Buffers                8892416 bytes

Database mounted.

Database opened.

 

 

--查看SCN号（第2个）

SYS@seiang11g>select current_scn from v$database;

 

CURRENT_SCN

-----------

1676913          (记录第二个SCN)

 

 

--再次插入10条测试数据

SYS@seiang11g>insert into seiang.test1 select level,lpad(level,20,'@') from dual connect by level <= 10;

10 rows created.

 

SYS@seiang11g>commit;

Commit complete.

 

SYS@seiang11g>insert into seiang.test2 select level,sysdate-50+level from dual connect by level <= 10;

10 rows created.

 

SYS@seiang11g>commit;

Commit complete.

 

SYS@seiang11g>select count(*) from seiang.test1;

 

  COUNT(*)

----------

       140

 

SYS@seiang11g>select count(*) from seiang.test2;

 

  COUNT(*)

----------

       140

 

--查看SCN号（第3个）

SYS@seiang11g>select current_scn from v$database;

 

CURRENT_SCN

-----------

1677000             (记录第三个SCN)

 

 

--测试query和flashback_scn（第一个SCN）导出

[oracle@seiang11g ~]$ expdp seiang/seiang directory=dumpdir dumpfile=seiang_test1_1.dmp tables=seiang.test1 query=seiang.test1:\"where id\<=50\" flashback_scn=1676032

 

Export: Release 11.2.0.4.0 - Production on Wed Aug 16 14:50:03 2017

 

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 "SEIANG"."SYS_EXPORT_TABLE_01":  seiang/******** directory=dumpdir dumpfile=seiang_test1_1.dmp tables=seiang.test1 query=seiang.test1:"where id<=50" flashback_scn=1676032

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 "SEIANG"."TEST1"                            6.757 KB      50 rows

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

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

Dump file set for SEIANG.SYS_EXPORT_TABLE_01 is:

  /u01/app/oracle/exp_imp_dump/seiang_test1_1.dmp

Job "SEIANG"."SYS_EXPORT_TABLE_01" successfully completed at Wed Aug 16 14:50:11 2017 elapsed 0 00:00:07

 

 

--测试query和flashback_scn（第2个SCN）导出

[oracle@seiang11g ~]$ expdp seiang/seiang directory=dumpdir dumpfile=seiang_test1_3.dmp tables=seiang.test1 query=seiang.test1:\"where id\<=50\" flashback_scn=1676913 

 

Export: Release 11.2.0.4.0 - Production on Wed Aug 16 14:51:58 2017

 

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 "SEIANG"."SYS_EXPORT_TABLE_01":  seiang/******** directory=dumpdir dumpfile=seiang_test1_3.dmp tables=seiang.test1 query=seiang.test1:"where id<=50" flashback_scn=1676913

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 "SEIANG"."TEST1"                            7.578 KB      80 rows

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

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

Dump file set for SEIANG.SYS_EXPORT_TABLE_01 is:

  /u01/app/oracle/exp_imp_dump/seiang_test1_3.dmp

Job "SEIANG"."SYS_EXPORT_TABLE_01" successfully completed at Wed Aug 16 14:52:05 2017 elapsed 0 00:00:06

 

--测试query和flashback_scn（第3个SCN）导出

[oracle@seiang11g ~]$ expdp seiang/seiang directory=dumpdir dumpfile=seiang_test1_2.dmp tables=seiang.test1 query=seiang.test1:\"where id\<=50\" flashback_scn=1677000

 

Export: Release 11.2.0.4.0 - Production on Wed Aug 16 14:51:10 2017

 

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 "SEIANG"."SYS_EXPORT_TABLE_01":  seiang/******** directory=dumpdir dumpfile=seiang_test1_2.dmp tables=seiang.test1 query=seiang.test1:"where id<=50" flashback_scn=1677000

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 "SEIANG"."TEST1"                            7.851 KB      90 rows

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

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

Dump file set for SEIANG.SYS_EXPORT_TABLE_01 is:

  /u01/app/oracle/exp_imp_dump/seiang_test1_2.dmp

Job "SEIANG"."SYS_EXPORT_TABLE_01" successfully completed at Wed Aug 16 14:51:17 2017 elapsed 0 00:00:06

 

 

--测试复杂query导出

 [oracle@seiang11g ~]$ expdp seiang/seiang directory=dumpdir dumpfile=seiang_test2_1.dmp tables=seiang.test2 query=seiang.test2:\"where id in \( select id from seiang.test2 where time\<sysdate\)\";                            

 

Export: Release 11.2.0.4.0 - Production on Wed Aug 16 14:56:31 2017

 

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 "SEIANG"."SYS_EXPORT_TABLE_01":  seiang/******** directory=dumpdir dumpfile=seiang_test2_1.dmp tables=seiang.test2 query=seiang.test2:"where id in ( select id from seiang.test2 where time<sysdate)"

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 "SEIANG"."TEST2"                            6.710 KB      90 rows

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

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

Dump file set for SEIANG.SYS_EXPORT_TABLE_01 is:

  /u01/app/oracle/exp_imp_dump/seiang_test2_1.dmp

Job "SEIANG"."SYS_EXPORT_TABLE_01" successfully completed at Wed Aug 16 14:56:38 2017 elapsed 0 00:00:06

 

 

--测试复杂query和flashback_scn（第1个SCN）导出

[oracle@seiang11g ~]$ expdp seiang/seiang directory=dumpdir dumpfile=seiang_test2_2.dmp tables=seiang.test2 query=seiang.test2:\"where id in \( select id from seiang.test2 where time\<sysdate\)\" flashback_scn=1676032;

 

Export: Release 11.2.0.4.0 - Production on Wed Aug 16 14:57:47 2017

 

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 "SEIANG"."SYS_EXPORT_TABLE_01":  seiang/******** directory=dumpdir dumpfile=seiang_test2_2.dmp tables=seiang.test2 query=seiang.test2:"where id in ( select id from seiang.test2 where time<sysdate)" flashback_scn=1676032

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 "SEIANG"."TEST2"                            6.125 KB      50 rows

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

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

Dump file set for SEIANG.SYS_EXPORT_TABLE_01 is:

  /u01/app/oracle/exp_imp_dump/seiang_test2_2.dmp

Job "SEIANG"."SYS_EXPORT_TABLE_01" successfully completed at Wed Aug 16 14:57:53 2017 elapsed 0 00:00:05

 

 

--测试复杂query和flashback_scn（第2个SCN）导出

[oracle@seiang11g ~]$ expdp seiang/seiang directory=dumpdir dumpfile=seiang_test2_3.dmp tables=seiang.test2 query=seiang.test2:\"where id in \( select id from seiang.test2 where time\<sysdate\)\" flashback_scn=1676913;

 

Export: Release 11.2.0.4.0 - Production on Wed Aug 16 14:58:23 2017

 

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 "SEIANG"."SYS_EXPORT_TABLE_01":  seiang/******** directory=dumpdir dumpfile=seiang_test2_3.dmp tables=seiang.test2 query=seiang.test2:"where id in ( select id from seiang.test2 where time<sysdate)" flashback_scn=1676913

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 "SEIANG"."TEST2"                            6.562 KB      80 rows

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

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

Dump file set for SEIANG.SYS_EXPORT_TABLE_01 is:

  /u01/app/oracle/exp_imp_dump/seiang_test2_3.dmp

Job "SEIANG"."SYS_EXPORT_TABLE_01" successfully completed at Wed Aug 16 14:58:29 2017 elapsed 0 00:00:05

 

--测试复杂query和flashback_scn（第3个SCN）导出

[oracle@seiang11g ~]$ expdp seiang/seiang directory=dumpdir dumpfile=seiang_test2_4.dmp tables=seiang.test2 query=seiang.test2:\"where id in \( select id from seiang.test2 where time\<sysdate\)\" flashback_scn=1677000;

 

Export: Release 11.2.0.4.0 - Production on Wed Aug 16 14:58:57 2017

 

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 "SEIANG"."SYS_EXPORT_TABLE_01":  seiang/******** directory=dumpdir dumpfile=seiang_test2_4.dmp tables=seiang.test2 query=seiang.test2:"where id in ( select id from seiang.test2 where time<sysdate)" flashback_scn=1677000

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 "SEIANG"."TEST2"                            6.710 KB      90 rows

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

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

Dump file set for SEIANG.SYS_EXPORT_TABLE_01 is:

  /u01/app/oracle/exp_imp_dump/seiang_test2_4.dmp

Job "SEIANG"."SYS_EXPORT_TABLE_01" successfully completed at Wed Aug 16 14:59:03 2017 elapsed 0 00:00:05

 

 

多个表使用query条件则使用','分开

[oracle@seiang11g ~]$ expdp seiang/seiang directory=dumpdir dumpfile=seiang_test1AND2.dmp tables=seiang.test1,seiang.test2 query=seiang.test1:\"where id\<=50\",seiang.test2:'"where id<=50"';

 

Export: Release 11.2.0.4.0 - Production on Wed Aug 16 15:03:47 2017

 

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 "SEIANG"."SYS_EXPORT_TABLE_01":  seiang/******** directory=dumpdir dumpfile=seiang_test1AND2.dmp tables=seiang.test1,seiang.test2 query=seiang.test1:"where id<=50",seiang.test2:"where id<=50"

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 128 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "SEIANG"."TEST1"                            7.851 KB      90 rows

. . exported "SEIANG"."TEST2"                            6.710 KB      90 rows

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

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

Dump file set for SEIANG.SYS_EXPORT_TABLE_01 is:

  /u01/app/oracle/exp_imp_dump/seiang_test1AND2.dmp

Job "SEIANG"."SYS_EXPORT_TABLE_01" successfully completed at Wed Aug 16 15:03:54 2017 elapsed 0 00:00:05

 

 

--测试多表query和flashback_scn（第1个SCN）导出

[oracle@seiang11g ~]$ expdp seiang/seiang directory=dumpdir dumpfile=seiang_test1AND2_1.dmp tables=seiang.test1,seiang.test2 query=seiang.test1:\"where id\<=50\",seiang.test2:'"where id<=50"' flashback_scn=1676032; 

 

Export: Release 11.2.0.4.0 - Production on Wed Aug 16 15:05:28 2017

 

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 "SEIANG"."SYS_EXPORT_TABLE_01":  seiang/******** directory=dumpdir dumpfile=seiang_test1AND2_1.dmp tables=seiang.test1,seiang.test2 query=seiang.test1:"where id<=50",seiang.test2:"where id<=50" flashback_scn=1676032

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 128 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "SEIANG"."TEST1"                            6.757 KB      50 rows

. . exported "SEIANG"."TEST2"                            6.125 KB      50 rows

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

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

Dump file set for SEIANG.SYS_EXPORT_TABLE_01 is:

  /u01/app/oracle/exp_imp_dump/seiang_test1AND2_1.dmp

Job "SEIANG"."SYS_EXPORT_TABLE_01" successfully completed at Wed Aug 16 15:05:34 2017 elapsed 0 00:00:05

 

 

--测试多表query和flashback_scn（第2个SCN）导出

[oracle@seiang11g ~]$ expdp seiang/seiang directory=dumpdir dumpfile=seiang_test1AND2_2.dmp tables=seiang.test1,seiang.test2 query=seiang.test1:\"where id\<=50\",seiang.test2:'"where id<=50"' flashback_scn=1676913;

 

Export: Release 11.2.0.4.0 - Production on Wed Aug 16 15:06:44 2017

 

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 "SEIANG"."SYS_EXPORT_TABLE_01":  seiang/******** directory=dumpdir dumpfile=seiang_test1AND2_2.dmp tables=seiang.test1,seiang.test2 query=seiang.test1:"where id<=50",seiang.test2:"where id<=50" flashback_scn=1676913

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 128 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "SEIANG"."TEST1"                            7.578 KB      80 rows

. . exported "SEIANG"."TEST2"                            6.562 KB      80 rows

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

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

Dump file set for SEIANG.SYS_EXPORT_TABLE_01 is:

  /u01/app/oracle/exp_imp_dump/seiang_test1AND2_2.dmp

Job "SEIANG"."SYS_EXPORT_TABLE_01" successfully completed at Wed Aug 16 15:06:49 2017 elapsed 0 00:00:04

 

 

--测试多表query和flashback_scn（第3个SCN）导出

[oracle@seiang11g ~]$ expdp seiang/seiang directory=dumpdir dumpfile=seiang_test1AND2_3.dmp tables=seiang.test1,seiang.test2 query=seiang.test1:\"where id\<=50\",seiang.test2:'"where id<=50"' flashback_scn=1677000;

 

Export: Release 11.2.0.4.0 - Production on Wed Aug 16 15:07:21 2017

 

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 "SEIANG"."SYS_EXPORT_TABLE_01":  seiang/******** directory=dumpdir dumpfile=seiang_test1AND2_3.dmp tables=seiang.test1,seiang.test2 query=seiang.test1:"where id<=50",seiang.test2:"where id<=50" flashback_scn=1677000

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 128 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "SEIANG"."TEST1"                            7.851 KB      90 rows

. . exported "SEIANG"."TEST2"                            6.710 KB      90 rows

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

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

Dump file set for SEIANG.SYS_EXPORT_TABLE_01 is:

  /u01/app/oracle/exp_imp_dump/seiang_test1AND2_3.dmp

Job "SEIANG"."SYS_EXPORT_TABLE_01" successfully completed at Wed Aug 16 15:07:25 2017 elapsed 0 00:00:04

 

 

 

 

 

 

以下是11g官方文档对flashback_scn、flashback_time、query参数的说明：

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

FLASHBACK_SCN

Default: There is no default

Purpose

Specifies the system change number (SCN) that Export will use to enable the Flashback Query utility.

指定导出将用于启用闪回查询实用程序的系统更改编号（SCN）。

Syntax and Description

FLASHBACK_SCN=scn_value

The export operation is perfORMed with data that is consistent up to the specified SCN. If the NETWORK_LINK parameter is specified, then the SCN refers to the SCN of the source database.

Restrictions

• FLASHBACK_SCN and FLASHBACK_TIME are mutually exclusive.
• The FLASHBACK_SCN parameter pertains only to the Flashback Query capability of Oracle Database. It is not applicable to Flashback Database, Flashback Drop, or Flashback Data ArcHive.

限制

FLASHBACK_SCN和FLASHBACK_TIME是互斥的。

FLASHBACK_SCN参数仅适用于Oracle数据库的闪回查询功能。它不适用于闪回数据库，闪回删除或闪回数据存档。

Example

The following example assumes that an existing SCN value of 384632 exists. It exports the hr schema up to SCN 384632.

> expdp hr DIRECTORY=dpump_dir1 DUMPFILE=hr_scn.dmp FLASHBACK_SCN=384632

Note:

If you are on a logical standby system and using a network link to access the logical standby primary, then the FLASHBACK_SCNparameter is ignored because SCNs are selected by logical standby.

 

 

FLASHBACK_TIME

Default: There is no default

Purpose

The SCN that most closely matches the specified time is found, and this SCN is used to enable the Flashback utility. The export operation is performed with data that is consistent up to this SCN.

找到与指定时间最匹配的SCN，此SCN用于启用闪回实用程序。导出操作使用与该SCN一致的数据执行。

Syntax and Description

FLASHBACK_TIME="TO_TIMESTAMP(time-value)"

Because the TO_TIMESTAMP value is enclosed in quotation marks, it would be best to put this parameter in a parameter file.

因为TO_TIMESTAMP值用引号括起来，最好将此参数放在参数文件中。

Restrictions

• FLASHBACK_TIME and FLASHBACK_SCN are mutually exclusive.
• The FLASHBACK_TIME parameter pertains only to the flashback query capability of Oracle Database. It is not applicable to Flashback Database, Flashback Drop, or Flashback Data Archive.

限制

FLASHBACK_TIME和FLASHBACK_SCN是互斥的。

FLASHBACK_TIME参数仅适用于Oracle数据库的闪回查询功能。它不适用于闪回数据库，闪回或闪回数据存档。

Example

You can specify the time in any format that the DBMS_FLASHBACK.ENABLE_AT_TIME procedure accepts. For example, suppose you have a parameter file,flashback.par, with the following contents:

DIRECTORY=dpump_dir1

DUMPFILE=hr_time.dmp

FLASHBACK_TIME="TO_TIMESTAMP('25-08-2008 14:35:00', 'DD-MM-YYYY HH24:MI:SS')"

You could then issue the following command:

> expdp hr PARFILE=flashback.par

The export operation will be performed with data that is consistent with the SCN that most closely matches the specified time.

Note:

If you are on a logical standby system and using a network link to access the logical standby primary, then the FLASHBACK_SCNparameter is ignored because SCNs are selected by logical standby. See Oracle Data Guard Concepts and Administration for information about logical standby databases.

 

 

QUERY

Default: There is no default

Purpose

Allows you to specify a query clause that is used to filter the data that gets exported.

Syntax and Description

QUERY = [schema.][table_name:] query_clause

The query_clause is typically a sql WHERE clause for fine-grained row selection, but could be any SQL clause. For example, an ORDER BY clause could be used to speed up a migration from a heap-organized table to an index-organized table. If a schema and table name are not supplied, then the query is applied to (and must be valid for) all tables in the export job. A table-specific query overrides a query applied to all tables.

query_clause通常是用于细粒度行选择的SQL WHERE子句，但也可以是任何SQL子句。例如，ORDER BY子句可用于加速从堆组织表到索引组织表的迁移。如果未提供schema和表名称，则该查询将应用于导出作业中的所有表（并且必须有效）。表特定的查询覆盖了应用于所有表的查询。

When the query is to be applied to a specific table, a colon must separate the table name from the query clause. More than one table-specific query can be specified, but only one query can be specified per table.

当查询应用于特定表时，冒号必须将表名与查询子句分开。可以指定多个表特定的查询，但是每个表只能指定一个查询。

If the NETWORK_LINK parameter is specified along with the QUERY parameter, then any objects specified in the query_clause that are on the remote (source) node must be explicitly qualified with the NETWORK_LINK value. Otherwise, Data Pump assumes that the object is on the local (target) node; if it is not, then an error is returned and the import of the table from the remote (source) system fails.

如果NETWORK_LINK参数与QUERY参数一起指定，则在远程（源）节点上的query_clause中指定的任何对象都必须使用NETWORK_LINK值明确限定。否则，数据泵假定对象在本地（目标）节点上;如果不是，则返回错误，并且从远程（源）系统导入表失败。

For example, if you specify NETWORK_LINK=dblink1, then the query_clause of the QUERY parameter must specify that link, as shown in the following example:

QUERY=(hr.employees:"WHERE last_name IN(SELECT last_name 

FROM hr.employees@dblink1)")

Depending on your operating system, the use of quotation marks when you specify a value for this parameter may also require that you use escape characters. Oracle recommends that you place this parameter in a parameter file, which can reduce the number of escape characters that might otherwise be needed on the command line. See "Use of Quotation Marks On the Data Pump Command Line".

To specify a schema other than your own in a table-specific query, you must be granted access to that specific table.

Restrictions

·         The QUERY parameter cannot be used with the following parameters:

o   CONTENT=METADATA_ONLY

o   ESTIMATE_ONLY

o   TRANSPORT_TABLESPACES

·         When the QUERY parameter is specified for a table, Data Pump uses external tables to unload the target table. External tables uses a SQL CREATE TABLE AS SELECT statement. The value of the QUERY parameter is the WHERE clause in the SELECT portion of the CREATE TABLE statement. If the QUERY parameter includes references to another table with columns whose names match the table being unloaded, and if those columns are used in the query, then you will need to use a table alias to distinguish between columns in the table being unloaded and columns in the SELECT statement with the same name. The table alias used by Data Pump for the table being unloaded is KU$.

QUERY参数不能与以下参数一起使用：

CONTENT = METADATA_ONLY

ESTIMATE_ONLY

TRANSPORT_TABLESPACES

当为表指定QUERY参数时，Data Pump使用外部表来卸载目标表。外部表使用SQL CREATE TABLE AS SELECT语句。 QUERY参数的值是CREATE TABLE语句的SELECT部分中的WHERE子句。如果QUERY参数包含对其名称与卸载表匹配的列的另一个表的引用，并且如果在查询中使用这些列，则需要使用表别名来区分要卸载的表中的列和具有相同名称的SELECT语句。 Data Pump为卸载的表使用的表别名为KU $。

For example, suppose you want to export a subset of the sh.sales table based on the credit limit for a customer in the sh.customers table. In the following example, KU$ is used to qualify the cust_id field in the QUERY parameter for unloading sh.sales. As a result, Data Pump exports only rows for customers whose credit limit is greater than $10,000.

QUERY='sales:"WHERE EXISTS (SELECT cust_id FROM customers c 

   WHERE cust_credit_limit > 10000 AND ku$.cust_id = c.cust_id)"'

If, as in the following query, KU$ is not used for a table alias, then the result will be that all rows are unloaded:

QUERY='sales:"WHERE EXISTS (SELECT cust_id FROM customers c 

   WHERE cust_credit_limit > 10000 AND cust_id = c.cust_id)"'

·         The maximum length allowed for a QUERY string is 4000 bytes including quotation marks, which means that the actual maximum length allowed is 3998 bytes.

Example

The following is an example of using the QUERY parameter:

> expdp hr PARFILE=emp_query.par

The contents of the emp_query.par file are as follows:

QUERY=employees:"WHERE department_id > 10 AND salary > 10000"

NOLOGFILE=YES 

DIRECTORY=dpump_dir1 

DUMPFILE=exp1.dmp 

This example unloads all tables in the hr schema, but only the rows that fit the query expression. In this case, all rows in all tables (except employees) in the hrschema will be unloaded. For the employees table, only rows that meet the query criteria are unloaded.


Oracle使用数据泵 (expdp/impdp)实施迁移


作者：SEian.G（苦练七十二变，笑对八十一难）