oracle11gexpdp工具的query參數(shù)使用

實(shí)際生產(chǎn)中導(dǎo)數(shù)據(jù)時(shí)可能會(huì)需要導(dǎo)部分?jǐn)?shù)據(jù)到測(cè)試庫(kù)上，expdp的query參數(shù)就可以完成這樣的需求。

創(chuàng)新互聯(lián)服務(wù)項(xiàng)目包括鹽津網(wǎng)站建設(shè)、鹽津網(wǎng)站制作、鹽津網(wǎng)頁(yè)制作以及鹽津網(wǎng)絡(luò)營(yíng)銷策劃等。多年來(lái)，我們專注于互聯(lián)網(wǎng)行業(yè)，利用自身積累的技術(shù)優(yōu)勢(shì)、行業(yè)經(jīng)驗(yàn)、深度合作伙伴關(guān)系等，向廣大中小型企業(yè)、政府機(jī)構(gòu)等提供互聯(lián)網(wǎng)行業(yè)的解決方案，鹽津網(wǎng)站推廣取得了明顯的社會(huì)效益與經(jīng)濟(jì)效益。目前，我們服務(wù)的客戶以成都為中心已經(jīng)輻射到鹽津省份的部分城市，未來(lái)相信會(huì)繼續(xù)擴(kuò)大服務(wù)區(qū)域并繼續(xù)獲得客戶的支持與信任！

數(shù)據(jù)庫(kù)版本

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)建測(cè)試表

zx@ORCL>create table e1 (id number,name varchar2(20));

Table created.

zx@ORCL>create table e2 (id number,birthday date);

Table created.

插入測(cè)試數(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

測(cè)試使用query導(dǎo)出

[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

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號(hào)

zx@ORCL>select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

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

2179047

zx@ORCL>select count(*) from e1;

COUNT(*)

----------

100

刪除部分?jǐn)?shù)據(jù)

zx@ORCL>delete from e1 where id<20;

19 rows deleted.

zx@ORCL>commit;

Commit complete.

zx@ORCL>host

測(cè)試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

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

測(cè)試復(fù)雜query導(dǎo)出

zx@ORCL>select count(*) from e1 where id in( select id from e2 where birthday<sysdate);

COUNT(*)

----------

[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\<sysdate$\"

Export: Release 11.2.0.4.0 - Production on Thu Jul 21 14:31:04 2016

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<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 "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

測(cè)試復(fù)雜query及flashback_scn導(dǎo)出

[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\<sysdate$\" flashback_scn=2179047

Export: Release 11.2.0.4.0 - Production on Thu Jul 21 14:32:07 2016

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<sysdate)" 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_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部分?jǐn)?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<sysdate);

COUNT(*)

----------

測(cè)試query及flashback_scn，結(jié)果只是對(duì)e1應(yīng)用flashback_snc，e2沒有應(yīng)用

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\<sysdate$\" flashback_scn=2179047

Export: Release 11.2.0.4.0 - Production on Thu Jul 21 14:33:55 2016

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<sysdate)" 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.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都應(yīng)用flashback_scn

zx@ORCL>select count(*) from e1 where id in( select id from e2 as of scn 2179047 where birthday<sysdate);

COUNT(*)

----------

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\<sysdate$\" flashback_scn=2179047

Export: Release 11.2.0.4.0 - Production on Thu Jul 21 14:39:52 2016

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<sysdate)" 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

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

網(wǎng)站題目：oracle11gexpdp工具的query參數(shù)使用
標(biāo)題URL：http://muchs.cn/article22/gedjcc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站內(nèi)鏈、響應(yīng)式網(wǎng)站、品牌網(wǎng)站建設(shè)、微信公眾號(hào)、用戶體驗(yàn)、面包屑導(dǎo)航

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容