这篇文章主要介绍了DATAPUMP如何导出时使用exclude排除表,具有一定借鉴价值,感兴趣的朋友可以参考下,希望大家阅读完这篇文章之后大有收获,下面让小编带着大家一起了解一下。一、问题描述 有个小小需求,要从生产数据库上导数据到测试库,问题是有张大表(30G)不需要导出到测试环境,所以要使用到expdp中’exclude’参数。但是在使用过程中还是碰到一点点question。二、操作过程1.根据expdp -help 信息中得知exclude参数的for example如下:EXCLUDEExclude 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.logexclude=table:”=’WSCT_DATA'” compression=allLRM-00116: syntax error at ‘table:’ following ‘=’3.修改,增加转义符后执行成功[oracle@rac2 ~]$ expdp cloan/xxxx DIRECTORY=backup DUMPFILE=cloan_20170713.dmp log=cloan_20170713.logEXCLUDE=TABLE:”= ‘WSCT_DATA'” compression=allExport: Release 11.2.0.4.0 – Production on Thu Jul 13 10:17:50 2017Copyright (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 ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing optionsLegacy 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=trueEstimate 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.dmpJob “CLOAN”.”SYS_EXPORT_SCHEMA_02″ successfully completed at Thu Jul 13 10:19:18 2017 elapsed 0 00:01:274.导入到测试环境成功[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 2017Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsMaster table “CLOANCP”.”SYS_IMPORT_FULL_01″ successfully loaded/unlo免费主机域名adedStarting “CLOANCP”.”SYS_IMPORT_FULL_01″:cloancp/******** DIRECTORY=backup DUMPFILE=cloan_20170713.dmp remap_schema=cloan:cloancp remap_tablespace=cloan:newcloanProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCEProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/TABLE_DATA……Processing object type SCHEMA_EXPORT/TABLE/COMMENTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/TRIGGERProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSJob “CLOANCP”.”SYS_IMPORT_FULL_01″ completed with 4 error免费主机域名(s) at Thu Jul 13 10:46:34 2017 elapsed 0 00:01:04感谢你能够认真阅读完这篇文章,希望小编分享的“DATAPUMP如何导出时使用exclude排除表”这篇文章对大家有帮助,同时也希望大家多多支持云技术,关注云技术行业资讯频道,更多相关知识等着你来学习!
相关推荐: PostgreSQL中Failover的配置方法是什么
本篇内容介绍了“PostgreSQL中Failover的配置方法是什么”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!PostgreSQL 10+的libpq版本…