DATAPUMP如何导出时使用exclude排除表

63次阅读
没有评论

共计 3219 个字符,预计需要花费 9 分钟才能阅读完成。

这篇文章主要介绍了 DATAPUMP 如何导出时使用 exclude 排除表,具有一定借鉴价值,感兴趣的朋友可以参考下,希望大家阅读完这篇文章之后大有收获,下面让丸趣 TV 小编带着大家一起了解一下。

一、问题描述

        有个小小需求,要从生产数据库上导数据到测试库,问题是有张大表(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

感谢你能够认真阅读完这篇文章,希望丸趣 TV 小编分享的“DATAPUMP 如何导出时使用 exclude 排除表”这篇文章对大家有帮助,同时也希望大家多多支持丸趣 TV,关注丸趣 TV 行业资讯频道,更多相关知识等着你来学习!

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-07-24发表,共计3219字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)