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

[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

时间： 2024-08-12 08:47:37

[20130105]expdp的include和exclude参数.txt的相关文章

[20151126]IMPDP TRANSFORM参数.TXT

[20151126]IMPDP TRANSFORM参数.TXT --最近要建立一个测试库,原来生产系统的一些表在定义时 STORAGE    (             INITIAL          8G             ....            ) NOPARALLEL; --实际我不需要建立这个大的INITIAL表,而且可能许多还是空的.有些我可能仅仅导入少量数据,这样要浪费大量磁盘空间,并且测试 --机器磁盘空间也不足.不能这样导入. --另外一个问题有一些表我设置PCT

Linux tar exclude参数的用法

      最近在对Oracle 数据库使用冷备tar迁移时,遇到需要将当前数据库文件下下的datapump导出的文件过滤掉,要不然然会产生很大的tar文件以及耗用网络传输时间.其实tar命令为我们提供了过滤功能,只不过由于过滤功能通常使用的比较少,所以很多人不知道.本文描述了tar命令下如何过滤不需要的文件或文件夹.   1.演示环境 #当前的tree 目录下存在BBB,CCC以及BNR子目录,现在需要将gz的dump文件过滤掉,也就是不参与打包 [oracle@linux1 ~]$ tree

[20150126]datadump的非文档参数.txt

[20150126]datadump的非文档参数.txt --总结一下expdp/impdp的非文档参数: 1. METRICS METRICS=Y ,记录执行详细的执行时间.例子: $ expdp scott/btbtms DIRECTORY=DATA_PUMP_DIR  DUMPFILE=emp012601.dmp LOGFILE=emp012601.log tables=emp metrics=y Export: Release 11.2.0.3.0 - Production on Mon

[20171206]SQLTUNE_CATEGORY参数.txt

[20171206]SQLTUNE_CATEGORY参数.txt --//今天提示别人使用sql profile优化语句,使用DBMS_SQLTUNE.import_sql_profile的引入替换功能.一般我的测试 --//category参数是'',也就是NULL.如果指定如何优化确定优化有效呢? --//链接:http://www.itpub.net/thread-2094823-1-1.html --//实际上就是修改参数SQLTUNE_CATEGORY. --//https://doc

[20170313]11G use_large_pages参数.txt

[20170313]11G use_large_pages参数.txt --11G 增加参数use_large_pages,可以灵活使用hugepages. 1.环境: SYS@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- --------------------------------------------

[20170209]理解pre_page_sga参数.txt

[20170209]理解pre_page_sga参数.txt --昨天测试pre_page_sga=true的情况: http://blog.itpub.net/267265/viewspace-2133198/ --//再次看看官方的定义: http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams201.htm#REFRN10174 PRE_PAGE_SGA determines whether Oracle reads

[20161123]failover与会话参数.txt

[20161123]failover与会话参数.txt --前几天itpub上有人问的问题,链接http://www.itpub.net/thread-2071933-1-1.html,开始我以为是会话没有退出. --实际上对方已经重启过数据库,不过后来我估计对方打开登录审计,从结果上猜测是正确的,不过我的问题是我想了解 --会话在支持failover时,重新连接上时会话参数是否还是原来的,还有审计是否还有效(在已经关闭相关审计的情况下). --测试是最好的证明,还是通过例子来说明问题. 1.环

[20171211]检查dg配置参数.txt

[20171211]检查dg配置参数.txt --//写一个脚本,用来检查dg配置参数. col name    for a30 col value   for a120 col ses_mod for a10 col sys_mod for a10 col ins_mod for a10 col type format 99999 SELECT p.name,        p.type,        p.value,        p.isses_modifiable      as SE

[20120918]exp要注意的问题CONSISTENT参数.txt

[20120918]exp要注意的问题CONSISTENT参数.txt     前几天开发要导出一个schema做测试,因为新程序改动太大,要求导出一份该schema下的全部数据,开发导入后出现 一些错误,反馈日志log后,才发现是主外键的问题,原来我忘记在导出时加入参数CONSISTENT=y. 缺省这个参数是N. 今天做一个测试看看差别在哪里: exp前先刷新共享池. alter system flush shared_pool; host exp sh/xxxxxx file=aaa.dm