Use of the query parameter in Oracle 11g EXPDP

The query parameter is provided in EXPDP, which can be used when you want to export part of the data in a table conditionally, as if it were used in the Where condition in the SELECT statement.

Database version

[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

Create a test table

[Email protected]>create table E1 (ID number,name varchar2 (20)); Table created. [Email protected]>create table E2 (ID number,birthday date); Table created.

Inserting test data

[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 to dual connect by level <= 100;100 rows created. [Email protected]>commit; Commit complete.

Create a Directory

[Email protected]>create directory dir as '/home/oracle/';D irectory created. [Email Protected]>host

Test Export using Query

Note: If the query condition is in Parfile, you do not need to escape with ' \ '

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

Query SCN number

[Email protected]>select dbms_flashback.get_system_change_number from dual;  Get_system_change_number------------------------2179047[email protected]>select Count (*) from E1; COUNT (*)----------100

Delete part of data

[Email protected]>delete from E1 where id<20;19 rows deleted. [Email protected]>commit; Commit complete. [Email Protected]>host

Test Query and 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

test Complex query Export

[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&nBsp 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

testing complex query and FLASHBACK_SCN export

[[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  (&NBSP;SELECT&NBSP;ID&NBSP;FROM&NBSp;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 "&NBSP;SUCCESSFULLY&NBSP;COMPLETED&NBSP;AT&NBsp thu jul 21 14:32:14 2016 elapsed 0 00:00:06[[email protected] ~]$  exitexit

Delete E2 Partial data

[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

Test query and FLASHBACK_SCN, the result is only for E1 application Flashback_snc,e2 no application

[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  (&NBSp;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

make E1 and E2 apply 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*************************************************** ***************************

Multiple tables use the query condition to use ', ' separate

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

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1881560

Use of the query parameter in Oracle 11g EXPDP