impdp/expdp with filtering

expdp/impdp通过query参数来过滤数据:
写在命令行上需要加转义符: query=\"where object_id\<1000 \"
写在parfile里不要加转义符:query="where object_id<1000 "
可以加多个"query="参数 
"query="参数的where条件后可以加order by 
"query="参数加表名只对此表有效(query=t1 表示过滤条件只对t1表有效); 不加表名对所有表有效, 没有指定过滤列的表会出错




expdp user2/user2 directory=pumpdir dumpfile=user2.dmp query=t1:\"where object_id\<1000 order by object_name\" query=\"where aaa \<500\" 


Export: Release 11.2.0.4.0 - Production on Wed Sep 21 20:38:54 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 "USER2"."SYS_EXPORT_SCHEMA_01":  user2/******** directory=pumpdir dumpfile=user2.dmp query=t1:"where query order query object_name" query="where aaa <500" 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.125 MB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "USER2"."T1"                                98.76 KB     997 rows
. . exported "USER2"."T2"                                8.796 KB     499 rows
ORA-31693: Table data object "USER2"."T3" failed to load/unload and is being skipped due to error:
ORA-00904: "AAA": invalid identifier
Master table "USER2"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for USER2.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/pumpdir/user2.dmp
Job "USER2"."SYS_EXPORT_SCHEMA_01" completed with 1 error(s) at Wed Sep 21 20:38:57 2016 elapsed 0 00:00:02


query=t1:\"where object_id\<1000 order by object_name\" 对t1有效
query=\"where aaa \<500\" 对t1以外的所有表有效, 但是t3没有"aaa", 所以出错(ORA-00904: "AAA": invalid identifier)




impdp user3/user3  remap_schema=user2:user3 directory=pumpdir dumpfile=user2.dmp \
query=user2.t1:\"where object_id\<10 order by object_name desc\" query=\"where aaa \<100\" 


Import: Release 11.2.0.4.0 - Production on Wed Sep 21 20:42:58 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
Master table "USER3"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "USER3"."SYS_IMPORT_FULL_01":  user3/******** remap_schema=user2:user3 directory=pumpdir dumpfile=user2.dmp query=user2.t1:"where query order query object_name query query="where query <100" 
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "USER3"."T1"                                98.76 KB       8 out of 997 rows
. . imported "USER3"."T2"                                8.796 KB      99 out of 499 rows
Job "USER3"."SYS_IMPORT_FULL_01" successfully completed at Wed Sep 21 20:42:59 2016 elapsed 0 00:00:01


sqlplus USER3/USER3


SQL> select object_name from t1;
OBJECT_NAME
--------------------------------------------------------------------------------
TAB$
I_TS#
I_OBJ#
I_FILE#_BLOCK#
C_TS#
C_OBJ#
C_FILE#_BLOCK#
CLU$


可见query=user2.t1:\"where object_id\<10 order by object_name desc\"的desc 生效

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/37279/viewspace-2125728/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/37279/viewspace-2125728/