对于数据泵EXPDP/IMPDP而言，功能比普通EXP/IMP功能要强的多，因此也可以实现一些普通导出导入工具很难完成的工作。



比如今天碰到的这个问题，要导出一些表，但是其中个别表只导出结构而不导出数据。

SQL> conn test/test


Connected.


SQL> set pages 100 lines 120


SQL> select count(*) from t;


 COUNT(*)


----------


       23


SQL> select count(*) from tt;


 COUNT(*)


----------


       72


SQL> exit


Disconnected from Oracle Database10gEnterprise Edition Release10.2.0.3.0 - 64bit Production


With the Partitioning, OLAP and Data Mining options


[oracle@yans1 ~]$ expdp test/test directory=d_output dumpfile=t_tt1.dp tables=(t,tt)


Export: Release10.2.0.3.0 - 64bit Production on星期二, 25 8月, 2009 16:04:58


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


Connected to: Oracle Database10gEnterprise Edition Release10.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=t_tt1.dp tables=(t,tt)


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/STATISTICS/TABLE_STATISTICS


. . exported "TEST"."T"                                  5.953 KB      23 rows


. . exported "TEST"."TT"                                 6.421 KB      72 rows


Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded


******************************************************************************


Dump file set for TEST.SYS_EXPORT_TABLE_01 is:


 /home/oracle/t_tt1.dp


Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 16:05:02


用T和TT表作为例子，分别代表需要导出结构的表和同时包含结构和数据的表。

这个需求对于普通的EXP/IMP来说，只能通过两次导出操作来完成，一次导出包含数据的表，另一个通过执行ROWS=N导出仅需要结构的表。

对于EXPDP来说，同样可以使用类似的方法，参数CONTENT控制导出的结构、数据还是全部：

[oracle@yans1 ~]$ expdp test/test directory=d_output dumpfile=t_tt2.dp tables=(t,tt) content=metadata_only


Export: Release10.2.0.3.0 - 64bit Production on星期二, 25 8月, 2009 16:32:59


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


Connected to: Oracle Database10gEnterprise Edition Release10.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=t_tt2.dp tables=(t,tt) content=metadata_only


Processing object type TABLE_EXPORT/TABLE/TABLE


Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS


Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded


******************************************************************************


Dump file set for TEST.SYS_EXPORT_TABLE_01 is:


 /home/oracle/t_tt2.dp


Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 16:33:02


但是这种方法控制的是整体，现在需要对其中的个别对象只导出表结构。最好想到的方法是通过QUERY来控制：

[oracle@yans1 ~]$ expdp test/test directory=d_output dumpfile=t_tt3.dp tables=(t,tt) query='t:"where 1=2"'


Export: Release10.2.0.3.0 - 64bit Production on星期二, 25 8月, 2009 16:51:37


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


Connected to: Oracle Database10gEnterprise Edition Release10.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=t_tt3.dp tables=(t,tt) query=t:"where 1=2"


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/STATISTICS/TABLE_STATISTICS


. . exported "TEST"."T"                                  5.507 KB       0 rows


. . exported "TEST"."TT"                                 6.421 KB      72 rows


Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded


******************************************************************************


Dump file set for TEST.SYS_EXPORT_TABLE_01 is:


 /home/oracle/t_tt.dp


Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 16:51:41


通过对T表添加一个恒为FALSE的查询条件，使得T表导出的时候获取0条记录，从而达到只导T的结构的目的。

但是这种方法对于数据量比较大的表效率会比较低，因为Oracle会进行导出的操作，只是在处理的时候将记录过滤掉，除了没有将数据写到导出文件，其他所有的操作都进行了，因此效率很低。

而实际上，数据泵还有更好的办法来解决这个问题：使用EXCLUDE参数。

[oracle@yans1 ~]$ expdp test/test directory=d_output dumpfile=t_tt4.dp tables=(t,tt) exclude=table/table_data:\"=\'T\'\"


Export: Release10.2.0.3.0 - 64bit Production on星期二, 25 8月, 2009 16:59:39


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


Connected to: Oracle Database10gEnterprise Edition Release10.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=t_tt4.dp tables=(t,tt) exclude=table/table_data:"='T'"


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/STATISTICS/TABLE_STATISTICS


. . exported "TEST"."TT"                                 6.421 KB      72 rows


Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded


******************************************************************************


Dump file set for TEST.SYS_EXPORT_TABLE_01 is:


 /home/oracle/t_tt4.dp


Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 16:59:43


这里看不到T表的信息，下面检查一下导出是否生效：

[oracle@yans1 ~]$ sqlplus test/test


SQL*Plus: Release10.2.0.3.0 - Production on星期二8月25 17:00:27 2009


Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.



Connected to:


Oracle Database10gEnterprise Edition Release10.2.0.3.0 - 64bit Production


With the Partitioning, OLAP and Data Mining options


SQL> desc t


Name                                      Null?    Type


----------------------------------------- -------- ----------------------------


ID                                                 NUMBER


NAME                                      NOT NULL VARCHAR2(30)


TYPE                                               VARCHAR2(7)


SQL> drop table t;


Table dropped.


SQL> exit


Disconnected from Oracle Database10gEnterprise Edition Release10.2.0.3.0 - 64bit Production


With the Partitioning, OLAP and Data Mining options


[oracle@yans1 ~]$ impdp test/test directory=d_output dumpfile=t_tt4.dp tables=t


Import: Release10.2.0.3.0 - 64bit Production on星期二, 25 8月, 2009 17:00:41


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


Connected to: Oracle Database10gEnterprise Edition Release10.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=t_tt4.dp tables=t


Processing object type TABLE_EXPORT/TABLE/TABLE


Processing object type TABLE_EXPORT/TABLE/TABLE_DATA


Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS


Job "TEST"."SYS_IMPORT_TABLE_01" successfully completed at 17:00:43


[oracle@yans1 ~]$ sqlplus test/test


SQL*Plus: Release10.2.0.3.0 - Production on星期二8月25 17:00:47 2009


Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.



Connected to:


Oracle Database10gEnterprise Edition Release10.2.0.3.0 - 64bit Production


With the Partitioning, OLAP and Data Mining options


SQL> desc t


Name                                      Null?    Type


----------------------------------------- -------- ----------------------------


ID                                                 NUMBER


NAME                                      NOT NULL VARCHAR2(30)


TYPE                                               VARCHAR2(7)


SQL> select * from t;


no rows selected


很显然，利用EXCLUDE的方式使得数据泵导出的时候去掉了T表的数据。


oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html