【EXP】备份复杂关联查询后的T表数据

本文是《【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 --

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

转载于:http://blog.itpub.net/519536/viewspace-629464/