本文是《【EXP】使用EXP的QUERY選項匯出表中部分資料》這篇文章的補充，進一步體現EXP工具在特定條件下匯出資料的功能。

1.T表資料內容如下
sec@secooler> select * from t;

         X Y
---------- ---------------------------------
         1 sec1
         2 sec2
         3 sec3
         4 sec4

2.建立另外一張關聯表T_REL，並初始化兩條記錄
sec@secooler> create table t_rel (x int);

Table created.

sec@secooler> insert into t_rel values (2);

1 row created.

sec@secooler> insert into t_rel values (3);

1 row created.

sec@secooler> commit;

Commit complete.

sec@secooler> select * From t_rel;

         X
----------
         2
         3

3.我們的目標是使用EXP獲取如下資料
sec@secooler> select t.* from t, t_rel where t.x=t_rel.x;

         X Y
---------- -------------------------------
         2 sec2
         3 sec3

4.方法如下，注意QUERY子句的書寫方法
secooler@secDB /exp$ exp sec/sec file=sec.dmp log=sec.log tables=t query=\"t,t_rel where t.x=t_rel.x\"

Export: Release 11.2.0.1.0 - Production on Mon Mar 8 23:20:00 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                              T          2 rows exported
Export terminated successfully without warnings.

5.使用PARFILE引數完成方法如下
1）編輯引數檔案
secooler@secDB /exp$ vi sec.par
userid=sec/sec
file=sec.dmp
log=sec.log
tables=t
query="t,t_rel where t.x=t_rel.x"
~
~

2）使用引數檔案完成資料匯出
secooler@secDB /exp$ exp parfile=sec.par

Export: Release 11.2.0.1.0 - Production on Mon Mar 8 23:27:24 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                              T          2 rows exported
Export terminated successfully without warnings.

6.匯入驗證
sys@secooler> conn sec/sec
Connected.
sec@secooler> delete from t;

4 rows deleted.

sec@secooler> commit;

Commit complete.

sec@secooler> exit

secooler@secDB /exp$ imp sec/sec file=sec.dmp ignore=y full=y

Import: Release 11.2.0.1.0 - Production on Mon Mar 8 23:30:30 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option

Export file created by EXPORT:V11.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SEC's objects into SEC
. importing SEC's objects into SEC
. . importing table                            "T"          2 rows imported
Import terminated successfully without warnings.
secooler@secDB /exp$ sqlplus sec/sec

SQL*Plus: Release 11.2.0.1.0 Production on Mon Mar 8 23:31:53 2010

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

sec@secooler> select * from t;

         X Y
---------- --------------------
         2 sec2
         3 sec3

成功，驗證完畢

7.小結
在使用EXP完成複雜關係條件下資料匯出時，尤其要注意在不同作業系統平臺上的轉義方法。建議使用引數檔案（結合PARFILE引數使用）規避這個不大不小的問題。

Good luck.

secooler
10.03.13

-- The End --

【EXP】備份複雜關聯查詢後的T表資料

相關文章