对于数据泵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
转载于:https://blog.51cto.com/19880614/1201505