實際生產中導數(shù)據(jù)時可能會需要導部分數(shù)據(jù)到測試庫上,expdp的query參數(shù)就可以完成這樣的需求。
創(chuàng)新互聯(lián)服務項目包括鹽津網(wǎng)站建設、鹽津網(wǎng)站制作、鹽津網(wǎng)頁制作以及鹽津網(wǎng)絡營銷策劃等。多年來,我們專注于互聯(lián)網(wǎng)行業(yè),利用自身積累的技術優(yōu)勢、行業(yè)經(jīng)驗、深度合作伙伴關系等,向廣大中小型企業(yè)、政府機構等提供互聯(lián)網(wǎng)行業(yè)的解決方案,鹽津網(wǎng)站推廣取得了明顯的社會效益與經(jīng)濟效益。目前,我們服務的客戶以成都為中心已經(jīng)輻射到鹽津省份的部分城市,未來相信會繼續(xù)擴大服務區(qū)域并繼續(xù)獲得客戶的支持與信任!
數(shù)據(jù)庫版本
zx@ORCL>select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE11.2.0.4.0Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
創(chuàng)建測試表
zx@ORCL>create table e1 (id number,name varchar2(20));
Table created.
zx@ORCL>create table e2 (id number,birthday date);
Table created.
插入測試數(shù)據(jù)
zx@ORCL>insert into e1 select level,lpad(level,20,'*') from dual connect by level <= 100;
100 rows created.
zx@ORCL>commit;
Commit complete.
zx@ORCL>insert into e2 select level,sysdate-50+level from dual connect by level <= 100;
100 rows created.
zx@ORCL>commit;
Commit complete.
創(chuàng)建目錄
zx@ORCL>create directory dir as '/home/oracle/';
Directory created.
zx@ORCL>host
測試使用query導出
[oracle@rhel6 ~]$ expdp zx/zx directory=dir dumpfile=e1.dmp tables=zx.e1 query=zx.e1:\"where id<=50\"
bash: =50": No such file or directory
Export: Release 11.2.0.4.0 - Production on Thu Jul 21 14:23:11 2016
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 "ZX"."SYS_EXPORT_TABLE_01": zx/******** directory=dir dumpfile=e1.dmp tables=zx.e1 query=zx.e1:"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 "ZX"."E1" 6.757 KB 50 rows
Master table "ZX"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ZX.SYS_EXPORT_TABLE_01 is:
/home/oracle/e1.dmp
Job "ZX"."SYS_EXPORT_TABLE_01" successfully completed at Thu Jul 21 14:23:26 2016 elapsed 0 00:00:11
exit
查詢scn號
zx@ORCL>select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
2179047
zx@ORCL>select count(*) from e1;
COUNT(*)
----------
100
刪除部分數(shù)據(jù)
zx@ORCL>delete from e1 where id<20;
19 rows deleted.
zx@ORCL>commit;
Commit complete.
zx@ORCL>host
測試query及flashback_scn
[oracle@rhel6 ~]$ expdp zx/zx directory=dir dumpfile=e1_1.dmp tables=zx.e1 query=zx.e1:\"where id\<=50\" flashback_scn=2179047
Export: Release 11.2.0.4.0 - Production on Thu Jul 21 14:25:41 2016
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 "ZX"."SYS_EXPORT_TABLE_01": zx/******** directory=dir dumpfile=e1_1.dmp tables=zx.e1 query=zx.e1:"where id<=50" flashback_scn=2179047
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 "ZX"."E1" 6.757 KB 50 rows
Master table "ZX"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ZX.SYS_EXPORT_TABLE_01 is:
/home/oracle/e1_1.dmp
Job "ZX"."SYS_EXPORT_TABLE_01" successfully completed at Thu Jul 21 14:25:49 2016 elapsed 0 00:00:06
[oracle@rhel6 ~]$ exit
exit
測試復雜query導出
zx@ORCL>select count(*) from e1 where id in( select id from e2 where birthday COUNT(*) ---------- 31 [oracle@rhel6 ~]$ expdp zx/zx directory=dir dumpfile=e1_2.dmp tables=zx.e1 query=zx.e1:\"where id in \( select id from e2 where birthday\ Export: Release 11.2.0.4.0 - Production on Thu Jul 21 14:31:04 2016 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 "ZX"."SYS_EXPORT_TABLE_01": zx/******** directory=dir dumpfile=e1_2.dmp tables=zx.e1 query=zx.e1:"where id in ( select id from e2 where birthday 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 "ZX"."E1" 6.242 KB 31 rows Master table "ZX"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for ZX.SYS_EXPORT_TABLE_01 is: /home/oracle/e1_2.dmp Job "ZX"."SYS_EXPORT_TABLE_01" successfully completed at Thu Jul 21 14:31:12 2016 elapsed 0 00:00:06 [oracle@rhel6 ~]$ exit exit zx@ORCL>host 測試復雜query及flashback_scn導出 [oracle@rhel6 ~]$ expdp zx/zx directory=dir dumpfile=e1_3.dmp tables=zx.e1 query=zx.e1:\"where id in \( select id from e2 where birthday\ Export: Release 11.2.0.4.0 - Production on Thu Jul 21 14:32:07 2016 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 "ZX"."SYS_EXPORT_TABLE_01": zx/******** directory=dir dumpfile=e1_3.dmp tables=zx.e1 query=zx.e1:"where id in ( select id from e2 where birthday 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 "ZX"."E1" 6.757 KB 50 rows Master table "ZX"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for ZX.SYS_EXPORT_TABLE_01 is: /home/oracle/e1_3.dmp Job "ZX"."SYS_EXPORT_TABLE_01" successfully completed at Thu Jul 21 14:32:14 2016 elapsed 0 00:00:06 [oracle@rhel6 ~]$ exit exit 刪除e2部分數(shù)據(jù) zx@ORCL>delete from e2 where id>25 and id<30; 4 rows deleted. zx@ORCL>commit; Commit complete. zx@ORCL>select count(*) from e1 where id in( select id from e2 where birthday COUNT(*) ---------- 27 測試query及flashback_scn,結果只是對e1應用flashback_snc,e2沒有應用 zx@ORCL>host [oracle@rhel6 ~]$ expdp zx/zx directory=dir dumpfile=e1_4.dmp tables=zx.e1 query=zx.e1:\"where id in \( select id from e2 where birthday\ Export: Release 11.2.0.4.0 - Production on Thu Jul 21 14:33:55 2016 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 "ZX"."SYS_EXPORT_TABLE_01": zx/******** directory=dir dumpfile=e1_4.dmp tables=zx.e1 query=zx.e1:"where id in ( select id from e2 where birthday 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 "ZX"."E1" 6.648 KB 46 rows Master table "ZX"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for ZX.SYS_EXPORT_TABLE_01 is: /home/oracle/e1_4.dmp Job "ZX"."SYS_EXPORT_TABLE_01" successfully completed at Thu Jul 21 14:34:03 2016 elapsed 0 00:00:06 [oracle@rhel6 ~]$ exit exit 使e1和e2都應用flashback_scn zx@ORCL>select count(*) from e1 where id in( select id from e2 as of scn 2179047 where birthday COUNT(*) ---------- 31 zx@ORCL>host [oracle@rhel6 ~]$ expdp zx/zx directory=dir dumpfile=e1_5.dmp tables=zx.e1 query=zx.e1:\"where id in \( select id from e2 as of scn 2179047 where birthday\ Export: Release 11.2.0.4.0 - Production on Thu Jul 21 14:39:52 2016 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 "ZX"."SYS_EXPORT_TABLE_01": zx/******** directory=dir dumpfile=e1_5.dmp tables=zx.e1 query=zx.e1:"where id in ( select id from e2 as of scn2179047 where birthday 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 "ZX"."E1" 6.757 KB 50 rows Master table "ZX"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ******************************************************************************
標題名稱:oracle11gexpdp工具的query參數(shù)使用
文章分享:http://weahome.cn/article/gedjcc.html