oracle exclude table,【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.