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

简介: [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


目录
相关文章
|
26天前
|
编译器 API C语言
C/C++ 获取文件名的方法:分享一些实用的获取文件名的方法和技巧(__FILE__,__builtin_FILE(),__BASE_FILE__等)
C/C++ 获取文件名的方法:分享一些实用的获取文件名的方法和技巧(__FILE__,__builtin_FILE(),__BASE_FILE__等)
34 0
|
5月前
指定的 filePath 文件不存在
指定的 filePath 文件不存在
21 2
完美解决common_define.h: No such file or directory
完美解决common_define.h: No such file or directory
125 0
完美解决common_define.h: No such file or directory
Cannot open include file: 'unistd.h': No such file or directory的解决办法
Cannot open include file: 'unistd.h': No such file or directory的解决办法
269 0
成功解决FileNotFoundError: [Errno 2] No such file or directory: '/home/bai/Myprojects/Tfexamples/data/kn
成功解决FileNotFoundError: [Errno 2] No such file or directory: '/home/bai/Myprojects/Tfexamples/data/kn
|
Python
6.4 file 的 with 用法
#!/usr/bin/env python # -*- coding:utf-8 -*- #@Time      :2017/10/28 9:13 #@Author    :zhouyuyao #@File      :file_with.
750 0
|
Python
6.2 file 写文件
#!/usr/bin/env python # -*- coding:utf-8 -*- #@Time      :2017/10/27 22:15 #@Author    :zhouyuyao #@File      :file_write.
665 0
|
Oracle 关系型数据库 OLAP
[20180224]expdp query 写法问题.txt
[20180224]expdp query 写法问题.txt --//如果使用expdp/impdp导入导出,如果参数复杂,最好的建议使用参数文件.避免各种问题.通过简单的例子说明问题.
1184 0
|
数据库 Windows Unix
EXCLUDE/INCLUDE
一、用法 1、单值 INCLUDE=TABLE:"= 'EMP'" INCLUDE=TABLE:"= 'DEPT'" 2、多值 INCLUDE=TABLE:"IN ('EMP', 'DEPT')" 3、模糊匹配 INCLUDE=TABLE:"LIKE '%E%'" INCLU...
1259 0