Oracle的匯入匯出工具EXP/IMP由來已久，大部分人對於使用這兩個工具也都不陌生。Oracle10g開始推出的資料泵EXPDP/IMPDP，提供了匯入匯出的效能，也增加了匯入匯出的功能，但是由於使用時間不長，可能部分人對這個工具不太瞭解。一直打算寫幾篇文章，簡單描述一下EXP/IMP和EXPDP/IMPDP的差異。

對比資料泵與原始匯入匯出工具（一）：http://yangtingkun.itpub.net/post/468/476017

對比資料泵與原始匯入匯出工具（二）：http://yangtingkun.itpub.net/post/468/476060

對比資料泵與原始匯入匯出工具（三）：http://yangtingkun.itpub.net/post/468/477335

對比資料泵與原始匯入匯出工具（四）：http://yangtingkun.itpub.net/post/468/477380

對比資料泵與原始匯入匯出工具（五）：http://yangtingkun.itpub.net/post/468/477633

這篇描述資料泵的QUERY引數。

去年年初開始了這個系列文章，當時正在看工具手冊，因此對資料泵和原始匯入匯出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

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

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

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

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

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

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

對比資料泵與原始匯入匯出工具（六）

相關文章