Oracle 11g expdp中query參數的使用

標籤：oracle expdp query

expdp中提供了query參數，可以在需要按條件匯出表中部分資料時使用，它的使用就像是在select語句中的where條件使用一樣。

資料庫版本

[email protected]>select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionPL/SQL Release 11.2.0.4.0 - ProductionCORE11.2.0.4.0ProductionTNS for Linux: Version 11.2.0.4.0 - ProductionNLSRTL Version 11.2.0.4.0 - Production

建立測試表

[email protected]>create table e1 (id number,name varchar2(20));Table created.[email protected]>create table e2 (id number,birthday date);Table created.

插入測試資料

[email protected]>insert into e1 select level,lpad(level,20,‘*‘) from dual connect by level <= 100;100 rows created.[email protected]>commit;Commit complete.[email protected]>insert into e2 select level,sysdate-50+level from dual connect by level <= 100;100 rows created.[email protected]>commit;Commit complete.

建立目錄

[email protected]>create directory dir as ‘/home/oracle/‘;Directory created.[email protected]>host

測試使用query匯出

注意：如果query條件在parfile中則不需要用‘\‘進行轉義

[[email protected] ~]$ expdp zx/zx directory=dir dumpfile=e1.dmp tables=zx.e1 query=zx.e1:\"where id<=50\"bash: =50": No such file or directoryExport: Release 11.2.0.4.0 - Production on Thu Jul 21 14:23:11 2016Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "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_DATATotal estimation using BLOCKS method: 64 KBProcessing object type TABLE_EXPORT/TABLE/TABLE. . exported "ZX"."E1"                                   6.757 KB      50 rowsMaster table "ZX"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded******************************************************************************Dump file set for ZX.SYS_EXPORT_TABLE_01 is:  /home/oracle/e1.dmpJob "ZX"."SYS_EXPORT_TABLE_01" successfully completed at Thu Jul 21 14:23:26 2016 elapsed 0 00:00:11exit

查詢scn號

[email protected]>select dbms_flashback.get_system_change_number from dual;GET_SYSTEM_CHANGE_NUMBER------------------------ 2179047[email protected]>select count(*) from e1;  COUNT(*)----------       100

刪除部分資料

[email protected]>delete from e1 where id<20;19 rows deleted.[email protected]>commit;Commit complete.[email protected]>host

測試query及flashback_scn

[[email protected] ~]$ expdp zx/zx directory=dir dumpfile=e1_1.dmp tables=zx.e1 query=zx.e1:\"where id\<=50\" flashback_scn=2179047Export: Release 11.2.0.4.0 - Production on Thu Jul 21 14:25:41 2016Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "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_DATATotal estimation using BLOCKS method: 64 KBProcessing object type TABLE_EXPORT/TABLE/TABLE. . exported "ZX"."E1"                                   6.757 KB      50 rowsMaster table "ZX"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded******************************************************************************Dump file set for ZX.SYS_EXPORT_TABLE_01 is:  /home/oracle/e1_1.dmpJob "ZX"."SYS_EXPORT_TABLE_01" successfully completed at Thu Jul 21 14:25:49 2016 elapsed 0 00:00:06[[email protected] ~]$ exitexit

測試複雜query匯出

[email protected]>select count(*) from e1 where id in( select id from e2 where birthday<sysdate);  COUNT(*)----------31[[email protected] ~]$ 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 2016Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "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_DATATotal estimation using BLOCKS method: 64 KBProcessing object type TABLE_EXPORT/TABLE/TABLE. . exported "ZX"."E1"                                   6.242 KB      31 rowsMaster table "ZX"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded******************************************************************************Dump file set for ZX.SYS_EXPORT_TABLE_01 is:  /home/oracle/e1_2.dmpJob "ZX"."SYS_EXPORT_TABLE_01" successfully completed at Thu Jul 21 14:31:12 2016 elapsed 0 00:00:06[[email protected] ~]$ exitexit[email protected]>host

測試複雜query及flashback_scn匯出

[[email protected] ~]$ 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=2179047Export: Release 11.2.0.4.0 - Production on Thu Jul 21 14:32:07 2016Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "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_DATATotal estimation using BLOCKS method: 64 KBProcessing object type TABLE_EXPORT/TABLE/TABLE. . exported "ZX"."E1"                                   6.757 KB      50 rowsMaster table "ZX"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded******************************************************************************Dump file set for ZX.SYS_EXPORT_TABLE_01 is:  /home/oracle/e1_3.dmpJob "ZX"."SYS_EXPORT_TABLE_01" successfully completed at Thu Jul 21 14:32:14 2016 elapsed 0 00:00:06[[email protected] ~]$ exitexit

刪除e2部分資料

[email protected]>delete from e2 where id>25 and id<30;4 rows deleted.[email protected]>commit;Commit complete.[email protected]>select count(*) from e1 where id in( select id from e2 where birthday<sysdate);  COUNT(*)----------27

測試query及flashback_scn，結果只是對e1應用flashback_snc，e2沒有應用

[email protected]>host[[email protected] ~]$ 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=2179047Export: Release 11.2.0.4.0 - Production on Thu Jul 21 14:33:55 2016Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "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_DATATotal estimation using BLOCKS method: 64 KBProcessing object type TABLE_EXPORT/TABLE/TABLE. . exported "ZX"."E1"                                   6.648 KB      46 rowsMaster table "ZX"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded******************************************************************************Dump file set for ZX.SYS_EXPORT_TABLE_01 is:  /home/oracle/e1_4.dmpJob "ZX"."SYS_EXPORT_TABLE_01" successfully completed at Thu Jul 21 14:34:03 2016 elapsed 0 00:00:06[[email protected] ~]$ exitexit

使e1和e2都應用flashback_scn

[email protected]>select count(*) from e1 where id in( select id from e2 as of scn 2179047 where birthday<sysdate);  COUNT(*)----------31[email protected]>host[[email protected] ~]$ 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=2179047Export: Release 11.2.0.4.0 - Production on Thu Jul 21 14:39:52 2016Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "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_DATATotal estimation using BLOCKS method: 64 KBProcessing object type TABLE_EXPORT/TABLE/TABLE. . exported "ZX"."E1"                                   6.757 KB      50 rowsMaster table "ZX"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded******************************************************************************

多個表使用query條件則使用‘,‘分開

[[email protected] ~]$ expdp system/123456 directory=dump dumpfile=query.dmp tables=zx.abc,zx.abce query=zx.abc:\"where id \< 4\",zx.abce:\"where id \< 4\"Export: Release 11.2.0.4.0 - Production on Fri Dec 9 16:13:41 2016Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsFLASHBACK automatically enabled to preserve database integrity.Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** directory=dump dumpfile=query.dmp tables=zx.abc,zx.abce query=zx.abc:"where id < 4",zx.abce:"where id < 4" Estimate in progress using BLOCKS method...Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 384 KBProcessing object type TABLE_EXPORT/TABLE/TABLE. . exported "ZX"."ABC"                                  5.898 KB       2 rows. . exported "ZX"."ABCE"                                 5.898 KB       2 rowsMaster table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded******************************************************************************Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:  /home/oracle/query.dmpJob "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Fri Dec 9 16:14:04 2016 elapsed 0 00:00:19

本文出自 “DBA Fighting!” 部落格，請務必保留此出處http://hbxztc.blog.51cto.com/1587495/1881560

Oracle 11g expdp中query參數的使用