溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
實驗環境
操作系統：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和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和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.
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（苦練七十二變，笑對八十一難）
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
