select * from v$version;BANNER..."/>
溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》

oracle 11g expdp工具的query參數(shù)使用

發(fā)布時(shí)間：2020-07-29 23:08:22 來源：網(wǎng)絡(luò) 閱讀：476 作者：hbxztc 欄目：關(guān)系型數(shù)據(jù)庫(kù)

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

數(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

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號(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

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

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

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

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

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

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<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(*)

----------

27

測(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

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<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(*)

----------

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

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

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


向AI問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手