备案控制台
探索云世界
开发者社区 数据库 文章 正文

[20130105]expdp的include和exclude参数.txt

2013-01-05 882
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： [20130105]expdp的include和exclude参数.txthttp://www.itpub.net/thread-1754104-1-1.html如果include,exclude参数很长，可以通过建立一张表来实现。
[20130105]expdp的include和exclude参数.txt

http://www.itpub.net/thread-1754104-1-1.html

如果include,exclude参数很长，可以通过建立一张表来实现。
自己做一些测试(注直接在命令行输入语法加入斜线，很烦!)：

1.使用include参数:
$ cat par.txt
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=scott.dmp
INCLUDE=TABLE:"IN (Select table_name from user_tables where table_name'SALES')"

$ expdp scott/XXXX parfile=par.txt
Export: Release 11.2.0.1.0 - Production on Sat Jan 5 15:01:28 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** parfile=par.txt
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.375 MB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/AUDIT_OBJ
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/USER_PREF_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION
. . exported "SCOTT"."T"                                 1.044 MB   10000 rows
. . exported "SCOTT"."DEPT"                              5.960 KB       5 rows
. . exported "SCOTT"."DEPT1"                             5.976 KB       6 rows
. . exported "SCOTT"."EMP"                               8.609 KB      15 rows
. . exported "SCOTT"."MV1"                               6.687 KB       4 rows
. . exported "SCOTT"."MV_SOURCE"                         6.242 KB      49 rows
. . exported "SCOTT"."MY_MV"                             6.242 KB      49 rows
. . exported "SCOTT"."CHAINED_IOT_ROWS"                      0 KB       0 rows
. . exported "SCOTT"."CHAINED_ROWS"                          0 KB       0 rows
. . exported "SCOTT"."MLOG$_MV_SOURCE"                       0 KB       0 rows
. . exported "SCOTT"."MLOG$_SALES"                           0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle11g/admin/test/dpdump/scott.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:01:55

修改par.txt文件：（再次测试前删除scott.bmp文件）

2.使用exclude参数:
$ cat par.txt
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=scott.dmp
EXCLUDE=TABLE:"IN (Select table_name from user_tables where table_name='SALES')"

$ expdp scott/XXXXX parfile=par.txt
Export: Release 11.2.0.1.0 - Production on Sat Jan 5 15:03:38 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** parfile=par.txt
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.375 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/AUDIT_OBJ
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/TABLE/MATERIALIZED_VIEW_LOG
Processing object type SCHEMA_EXPORT/JOB
. . exported "SCOTT"."T"                                 1.044 MB   10000 rows
. . exported "SCOTT"."DEPT"                              5.960 KB       5 rows
. . exported "SCOTT"."DEPT1"                             5.976 KB       6 rows
. . exported "SCOTT"."EMP"                               8.609 KB      15 rows
. . exported "SCOTT"."MV1"                               6.687 KB       4 rows
. . exported "SCOTT"."MV_SOURCE"                         6.242 KB      49 rows
. . exported "SCOTT"."MY_MV"                             6.242 KB      49 rows
. . exported "SCOTT"."CHAINED_IOT_ROWS"                      0 KB       0 rows
. . exported "SCOTT"."CHAINED_ROWS"                          0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle11g/admin/test/dpdump/scott.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:04:44

注意include以及exclude的区别：
INCLUDE=TABLE:"IN (Select table_name from user_tables where table_name'SALES')"
EXCLUDE=TABLE:"IN (Select table_name from user_tables where table_name='SALES')"

前者expdp仅仅包括需要的表。其他对象不包括,比如job,MATERIALIZED_VIEW_LOG.
后面仅仅排斥对应的表。

3.测试include的not in是否可行。
$ cat par.txt
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=scott.dmp
INCLUDE=TABLE:"NOT IN (Select table_name from user_tables where table_name='SALES')"

$ expdp scott/XXXX parfile=par.txt

Export: Release 11.2.0.1.0 - Production on Sat Jan 5 15:13:17 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** parfile=par.txt
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.375 MB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/AUDIT_OBJ
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION
. . exported "SCOTT"."T"                                 1.044 MB   10000 rows
. . exported "SCOTT"."DEPT"                              5.960 KB       5 rows
. . exported "SCOTT"."DEPT1"                             5.976 KB       6 rows
. . exported "SCOTT"."EMP"                               8.609 KB      15 rows
. . exported "SCOTT"."MV1"                               6.687 KB       4 rows
. . exported "SCOTT"."MV_SOURCE"                         6.242 KB      49 rows
. . exported "SCOTT"."MY_MV"                             6.242 KB      49 rows
. . exported "SCOTT"."CHAINED_IOT_ROWS"                      0 KB       0 rows
. . exported "SCOTT"."CHAINED_ROWS"                          0 KB       0 rows
. . exported "SCOTT"."MLOG$_MV_SOURCE"                       0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle11g/admin/test/dpdump/scott.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:13:41


文章标签：
关系型数据库
Oracle
OLAP
lfreeali
目录
相关文章
泡沫o0
|
1月前
|
编译器 API C语言
C/C++ 获取文件名的方法：分享一些实用的获取文件名的方法和技巧（__FILE__,__builtin_FILE(),__BASE_FILE__等）
C/C++ 获取文件名的方法：分享一些实用的获取文件名的方法和技巧（__FILE__,__builtin_FILE(),__BASE_FILE__等）
泡沫o0
44 0
vohelon
|
5月前
指定的 filePath 文件不存在
指定的 filePath 文件不存在
vohelon
23 2
柳鲲鹏
Cannot open include file: 'unistd.h': No such file or directory的解决办法
Cannot open include file: 'unistd.h': No such file or directory的解决办法
柳鲲鹏
273 0
游客dk64xrhekz3ko
|
Shell
Shell函数、系统函数、basename [string / pathname] [suffix] 可以理解为取路径里的文件名称 、dirname 文件绝对路径、自定义函数
Shell函数、系统函数、basename [string / pathname] [suffix] 可以理解为取路径里的文件名称 、dirname 文件绝对路径、自定义函数
游客dk64xrhekz3ko
112 0
Shell函数、系统函数、basename [string / pathname] [suffix] 可以理解为取路径里的文件名称 、dirname 文件绝对路径、自定义函数
小周啊
|
Python
6.4 file 的 with 用法
#!/usr/bin/env python # -*- coding:utf-8 -*- #@Time      :2017/10/28 9:13 #@Author    :zhouyuyao #@File      :file_with.
小周啊
751 0
小周啊
|
Python
6.2 file 写文件
#!/usr/bin/env python # -*- coding:utf-8 -*- #@Time      :2017/10/27 22:15 #@Author    :zhouyuyao #@File      :file_write.
小周啊
666 0
lfreeali
|
Oracle 关系型数据库 OLAP
[20180224]expdp query 写法问题.txt
[20180224]expdp query 写法问题.txt --//如果使用expdp/impdp导入导出,如果参数复杂,最好的建议使用参数文件.避免各种问题.通过简单的例子说明问题.
lfreeali
1184 0
ryan_bai
|
数据库 Windows Unix
EXCLUDE/INCLUDE
一、用法 1、单值 INCLUDE=TABLE:"= 'EMP'" INCLUDE=TABLE:"= 'DEPT'" 2、多值 INCLUDE=TABLE:"IN ('EMP', 'DEPT')" 3、模糊匹配 INCLUDE=TABLE:"LIKE '%E%'" INCLU...
ryan_bai
1260 0
lfreeali
|
缓存 Oracle 关系型数据库
[20171122]rman filesystemio_options.txt
[20171122]rman备份与参数filesystemio_options设置.txt --//这几天写了几篇filesystemio_options参数设置的文章,单独测试这个参数对rman备份以及os缓存的影响.
lfreeali
959 0
技术小美
|
PHP
is_file函数
技术小美
980 0

热门文章

最新文章

  • 1
    Mac安装并使用telnet命令操作
  • 2
    OSS回源的几种方式和应用场景
  • 3
    [剑指offer] 孩子们的游戏(圆圈中最后剩下的数)
  • 4
    网络安全系列之二十二 Windows用户账号加固
  • 5
    我理解的一个程序员如何学习前端开发
  • 6
    《社交网站界面设计（原书第2版）》——1.9　为设备之间的空间进行设计
  • 7
    《Microduino实战》——1.2　为什么要开源
  • 8
    .Net函数Math.Round你会用吗？
  • 9
    麻省理工大学新发明：暗黑WiFi透视技术
  • 10
    2014秋C++第19周 补充代码 哈希法的存储与查找
  • 1
    R语言关联规则模型(Apriori算法)挖掘杂货店的交易数据与交互可视化
    25
  • 2
    R语言近似贝叶斯计算MCMC（ABC-MCMC）轨迹图和边缘图可视化
    21
  • 3
    r语言中对LASSO回归，Ridge岭回归和弹性网络Elastic Net模型实现-4
    29
  • 4
    Sentieon | 每周文献-Multi-omics-第四十一期
    25
  • 5
    数据分享|R语言广义线性模型GLM：线性最小二乘、对数变换、泊松、二项式逻辑回归分析冰淇淋销售时间序列数据和模拟-2
    15
  • 6
    数据分享|R语言广义线性模型GLM：线性最小二乘、对数变换、泊松、二项式逻辑回归分析冰淇淋销售时间序列数据和模拟-1
    22
  • 7
    基于RT-Thread摄像头车牌图像采集系统
    21
  • 8
    R语言极值理论：希尔HILL统计量尾部指数参数估计可视化
    15
  • 9
    【视频】R语言中的分布滞后非线性模型（DLNM）与发病率，死亡率和空气污染示例
    18
  • 10
    sql语句创建数据库
    17

    • 相关电子书

    更多
  • 低代码开发师（初级）实战教程
  • 冬季实战营第三期：MySQL数据库进阶实战
  • 阿里巴巴DevOps 最佳实践手册
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）