去年年初开始了这个系列文章，当时正在看工具手册，因此对数据泵和原始导入导出EXP/IMP的差异比较有心得，不过当时只写了一部分，这两天整理的时候发现，于是打算再续写几篇。

Oracle在EXP/IMP时已经支持QUERY参数，不过那是的QUERY参数要求比较严格，必须导出的所有表都满足QUERY参数的查询条件，使得QUERY参数的使用有很大的局限性：

SQL> CREATE TABLE T1                             
  2  (ID NUMBER PRIMARY KEY,
  3  NAME VARCHAR2(30),
  4  TYPE VARCHAR2(30));

Table created.

SQL> CREATE TABLE T2
  2  (ID NUMBER PRIMARY KEY,
  3  FID NUMBER,
  4  NAME VARCHAR2(30),
  5  CONSTRAINT FK_T2 FOREIGN KEY (FID) REFERENCES T1 (ID));

Table created.

SQL> INSERT INTO T1
  2  VALUES (1, 'T1', 'TABLE');

1 row created.

SQL> INSERT INTO T1
  2  VALUES (2, 'T2', 'TABLE');

1 row created.

SQL> INSERT INTO T1
  2  VALUES (3, 'FK_T2', 'CONSTRAINT');

1 row created.

SQL> INSERT INTO T2
  2  VALUES (1, 1, 'PK_T1');

1 row created.

SQL> INSERT INTO T2
  2  VALUES (2, 2, 'PK_T2');

1 row created.

SQL> COMMIT;

Commit complete.

[oracle@yans1 ~]$ exp test/test file=t1_t2.dmp tables=t1,t2 query=\"where id = 1\"

Export: Release 10.2.0.3.0 - Production on 星期一 8月 23 22:42:20 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                             T1          1 rows exported
. . exporting table                             T2          1 rows exported
Export terminated successfully without warnings.
[oracle@yans1 ~]$ exp test/test file=t1_t2.dmp tables=t1,t2 query=\"where type = \'TABLE\'\"

Export: Release 10.2.0.3.0 - Production on 星期一 8月 23 22:42:48 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                             T1          2 rows exported
. . exporting table                             T2
EXP-00056: ORACLE error 904 encountered
ORA-00904: "TYPE": invalid identifier
Export terminated successfully with warnings.

对于T1和T2都成立的查询WHERE ID = 1，使用QUERY语句可以正常工作，而对于WHERE TYPE = ‘TABLE’来说，只对T1成立，因此导出T2时报错。

而数据泵则没有这个限制，数据泵在指定QUERY语句的时候需要明确指定表名，因此完全可以实现上面的功能：

[oracle@yans1 ~]$ expdp test/test directory=d_output dumpfile=t1_t2.dp tables=t1,t2 query="T1:\"WHERE TYPE = 'TABLE'\""

Export: Release 10.2.0.3.0 - 64bit Production on 星期一, 23 8月, 2010 23:00:32

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "TEST"."SYS_EXPORT_TABLE_01":  test/******** directory=d_output dumpfile=t1_t2.dp tables=t1,t2 query=T1:"WHERE TYPE = 'TABLE'"
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "TEST"."T1"                                 5.539 KB       2 rows
. . exported "TEST"."T2"                                 5.539 KB       2 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
  /home/oracle/t1_t2.dp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 23:00:36

由于数据泵的这个功能，可以为不同的表指定不同的查询语句，比如：

[oracle@yans1 ~]$ expdp test/test directory=d_output dumpfile=t1_t2_2.dp tables=t1,t2 query='T1:"WHERE ID = 1"' query='T2:"WHERE FID = 1"'

Export: Release 10.2.0.3.0 - 64bit Production on 星期一, 23 8月, 2010 23:03:28

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "TEST"."SYS_EXPORT_TABLE_01":  test/******** directory=d_output dumpfile=t1_t2_2.dp tables=t1,t2 query=T1:"WHERE ID = 1" query=T2:"WHERE FID = 1"
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "TEST"."T1"                                 5.523 KB       1 rows
. . exported "TEST"."T2"                                 5.523 KB       1 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
  /home/oracle/t1_t2_2.dp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 23:03:32

数据泵的QUERY功能的强大之处还体现在，不仅EXPDP支持，IMPDP也支持查询参数，而对于原始导入导出工具来说，只有EXP支持QUERY参数：

[oracle@yans1 ~]$ expdp test/test directory=d_output dumpfile=t2.dp tables=t2

Export: Release 10.2.0.3.0 - 64bit Production on 星期一, 23 8月, 2010 23:13:17

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "TEST"."SYS_EXPORT_TABLE_01":  test/******** directory=d_output dumpfile=t2.dp tables=t2
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "TEST"."T2"                                 5.539 KB       2 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
  /home/oracle/t2.dp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 23:13:21

导出T2全部，下面TRUNCATE T2表：

SQL> TRUNCATE TABLE T2;

Table truncated.

使用QUERY方式导入部分数据：

[oracle@yans1 ~]$ impdp test/test directory=d_output dumpfile=t2.dp tables=t2 query='T2:"WHERE ID = 1"' table_exists_action=append

Import: Release 10.2.0.3.0 - 64bit Production on 星期一, 23 8月, 2010 23:14:56

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "TEST"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_TABLE_01":  test/******** directory=d_output dumpfile=t2.dp tables=t2 query=T2:"WHERE ID = 1" table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39152: Table "TEST"."T2" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."T2"                                 5.539 KB       1 out of 2 rows
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "TEST"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 23:14:58