【DATAPUMP】导出时使用exclude排除表

一、问题描述
今天有个小小需求,要从生产数据库上导数据到测试库,问题是有张大表(30G)不需要导出到测试环境,所以要使用到expdp中’exclude’参数。但是在使用过程中还是碰到一点点question。

二、操作过程
1.根据expdp -help 信息中得知exclude参数的for example如下:
EXCLUDE
Exclude specific object types.
For example, EXCLUDE=SCHEMA:”=’HR’”.

当然 = 也可以替换成 in ( ) 或 like ‘%..%’这种方式,如:exclude=index:”like ‘emp%’” ,也可以只给object types,如: exclude=VIEW,PACKAGE, FUNCTION
官方文档给出的Syntax是
EXCLUDE=object_type[:name_clause] [, …]

按照上面的例子,我推断出exclude参数针对表的使用
exclude=table:”=‘table name’”
2.执行语句报错,此处说明语法有问题,最终查询到原来是由于没有加转义符。

[oracle@rac2 ~]$ expdp cloan/xxxx DIRECTORY=backup DUMPFILE=cloan_20170713.dmp log=cloan_20170713.log  exclude=table:"='WSCT_DATA'" compression=all
LRM-00116: syntax error at 'table:' following '='

3.修改,增加转义符后执行成功

[oracle@rac2 ~]$ expdp cloan/xxxx DIRECTORY=backup DUMPFILE=cloan_20170713.dmp log=cloan_20170713.log  EXCLUDE=TABLE:\"= \'WSCT_DATA\'\" compression=all

Export: Release 11.2.0.4.0 - Production on Thu Jul 13 10:17:50 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "log=cloan_20170713.log" Location: Command Line, Replaced with: "logfile=cloan_20170713.log"
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting "CLOAN"."SYS_EXPORT_SCHEMA_02":  cloan/******** DIRECTORY=backup DUMPFILE=cloan_20170713.dmp logfile=cloan_20170713.log EXCLUDE=TABLE:"= 'WSCT_DATA'" compression=all reuse_dumpfiles=true 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
… … 
Dump file set for CLOAN.SYS_EXPORT_SCHEMA_02 is:
  /backup/cloan_20170713.dmp
Job "CLOAN"."SYS_EXPORT_SCHEMA_02" successfully completed at Thu Jul 13 10:19:18 2017 elapsed 0 00:01:27

4.导入到测试环境成功

[oracle@testdb backup]$  impdp cloancp/xxxx DIRECTORY=backup DUMPFILE=cloan_20170713.dmp remap_schema=cloan:cloancp remap_tablespace=cloan:newcloan;

Import: Release 11.2.0.4.0 - Production on Thu Jul 13 10:45:30 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "CLOANCP"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "CLOANCP"."SYS_IMPORT_FULL_01":  cloancp/******** DIRECTORY=backup DUMPFILE=cloan_20170713.dmp remap_schema=cloan:cloancp remap_tablespace=cloan:newcloan
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
…...
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "CLOANCP"."SYS_IMPORT_FULL_01" completed with 4 error(s) at Thu Jul 13 10:46:34 2017 elapsed 0 00:01:04

三、总结
一个小小问题,使得对expdp的参数exclude加以学习与掌握。重点是单引号’ 双引号“ 都需要进行转义才可以使命令正常执行。 Where there is a will, there is a way.

关于sam

ORACLE技术的忠实Fans! 曾就职于中国铁道科学研究院电子计算技术研究所,负责全国客车与货车行车安全信息化系统数据库及系统运维工作。太极计算机股份有限公司,负责北京、天津、冀北、华北、上海国家电网数据库部署、迁移、升级、灾备等实施规划工作。大唐国际30多家发电厂、北京市公安局数据库运维及优化工作。 合力中税科技发展有限公司现,负责oracle,mysql,hadoop数据库相关所有工作。现就就任于金融工场DBA职位,负责公司oracle,mysql数据库相关所有工作。 拥有Oracle技术10g/11g的 OCP与OCM认证,获得YEP(Oracle用户组年轻专家项目)的称号,OCM联盟成员,SDOUG成员。
此条目发表在expdp/impdp, oracle分类目录,贴了, 标签。将固定链接加入收藏夹。

发表评论