实验环境
操作系统：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（苦练七十二变，笑对八十一难）
