数据泵避免个别表数据的导出

 

对于数据泵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表的数据。