expdp及impdp中的exclude及include参数_expdp exclude

expdp 及imdpd命令中，exclude及include参数还是有一些要注意的地方，特别是涉及选择性条件时。

一、通用

1、exclude及include参数不能同时使用，这两个是相互排斥的。

2、在parfile参数文件中，可以同时用多个exclude参数，但只能用一个include参数,(exclude及include参数不能同时使用)

3、include、exclude参数是默认就是针对名称操作的：如表名、视图名、过程名、包名等，所以设计条件时，可以从查询语句中先取得这些名称。

4、include、exclude参数中，在escape语句中，不能用\作为转义符

   (1)、include=table:"like 'SEC_%'" 
     结果：SECAAAROLE、SEC_ROLE、SEC_OPERATION三个表，说明，_依旧表示一个占位符的作用

   （2）、include=table:"like 'SEC\_%'"
       不能导出SECAAAROLE、SEC_ROLE、SEC_OPERATION三个表，说明在''中,\并不表示转义符

   （3）、include=table:"like 'SEC\_%'escape'\'"  这样会报错：
       ORA-39001: invalid argument value
       ORA-39071: Value for INCLUDE is badly formed.
       ORA-01425: escape character must be character string of length 1
    （4）、改成这样  include=table:"like 'SEC#_%'escape'#'"
         可以正确导出SEC_ROLE、SEC_OPERATION二个表，但不能导出SECAAAROLE这个表。结论：在include、exclude参数中，在escape语句中，不能用\作为转义符！！，可以选用选用其他特殊字符作为转义符。如果确实要用\,也要可以用ascii码代替：include=table:"like 'SEC\_%'escape chr(92)"

二、exclude参数用法

a、exclude参数在parfile文件中可以有多个，还有多种写法。

[oracle@test189 temp2]$ vi zzw-expscript_impclude.par 
DIRECTORY=ZZW_EXPDPDIR
DUMPFILE=bdctemp1.dmp
exclude=table:"like 'BDC%'" , table:"like 'USPC%'",table:"like 'AUDIT%'"
exclude=table:"like 'SMS#_%'escape'#'"
exclude=table:"in (select table_name from user_tables  where regexp_like(table_name,'^MENU.*')
or regexp_like(table_name,'^SEC_.*_.*$'))"
LOGFILE=bdctemp1.log

b、支持换行，比如，上面的语句，在parfile文件中如下换行也是可以的

[oracle@test189 temp2]$ vi zzw-expscript_impclude.par 
DIRECTORY=ZZW_EXPDPDIR
DUMPFILE=bdctemp1.dmp
EXCLUDE=STATISTICS
exclude=view,table:"like 'BDC%'" ,
table:"like 'USPC%'",
table:"like 'AUDIT%'"
exclude=table:"like 'SMS#_%'escape'#'"
exclude=table:"in (select table_name from user_tables  where regexp_like(table_name,'^MENU.*') 
or regexp_like(table_name,'^SEC_.*_.*$'))"
LOGFILE=bdctemp1.log

    ps:采用这种exclude=table:"in (select table_name from user_tables)"方法导出时，我环境中会出现 ORA-07445: exception encountered: core dump     [kokemisz()+34] [SIGSEGV] [ADDR:0x18] [PC:0x143F5B6] [Address not mapped to object] []  这样的错误，在parfile文件中加入 EXCLUDE=STATISTICS条件问题就解决了。
 

三、include参数用法

a、不允许的写法

    include=table:"='BOSS'" or table:"='SEC_ROLE'"

    include=table:"='BOSS'" , table:"='SEC_ROLE'"

b、允许的写法
     include=table:"='BOSS'"

    include=table:"in('BOSS','SEC_ROLE')"

    include=table:"in(select table_name from user_tables  where table_name in('BOSS','SEC_ROLE'))"

    include=table:"in(select table_name from user_tables  where regexp_like(table_name,'^BDC_.{4}_.*$'))"   #注意，_在like中表示占位符，在regexp_like不表示占位符。 

     include=table:"in(select table_name from user_tables  where regexp_like(table_name,'^BDC_.{8}_.*$') or regexp_like(table_name,'^ATTACHMENT_.{4}') or  table_name like 'QRTZ#_%'escape'#')"

c、建立表exp_table
    create table exp_table (table_name varchar2(100);
    然后将需要导出的表名插入exp_table中。
    insert into exp_table values(‘A’);
    insert into exp_table values(‘B’);
    insert into exp_table values(‘PUB_GOODS’);
    insert into exp_table values(‘PUB_GOODS_UNIT’);

    最后导出的的时候：
    parfile
    userid=user/passwd
    directory=expdir
    dumpfile=testfile.dmp
    include=table:" in (select table_name from exp_table ) "
    这样就可以导出exp_table中所包含的所有表了。更神奇的是，可以在exp_table里面将自己也插入进去，然后把exp_table也导出哦

-----------------------------

 Exporting or Importing a large number of objects.

If a large number of objects needs to be exported (or imported), it is possible that an internal buffer limit is exceeded (e.g. for the parameters INCLUDE or TABLES). If that happens it will cause the Data Pump job to abort with an error such as: ORA-06502 (PL/SQL: numeric or value error: character string buffer too small). This happened especially in Oracle10g Release 1 where the value for the internal buffer was set to 3000 bytes. With the fix for Bug 4053129 "EXPDP fails with ORA-39125 ORA-6502 on large list of table names" (not a public bug; fixed in 10.1.0.5.0 and 10.2.0.x), this value was increased to 4000 bytes.
When exporting a large number of objects, we recommend to make use of a table inside the database that contains the names of the objects.
Example:

-- create a table that contains the names of the objects:

CONNECT scott/password
CREATE TABLE expdp_tab (owner VARCHAR2(30),
   object_name VARCHAR2(128), object_type VARCHAR2(19));
INSERT INTO expdp_tab VALUES ('SCOTT','EMP','TABLE');
INSERT INTO expdp_tab VALUES ('SCOTT','DEPT','TABLE');
INSERT INTO expdp_tab VALUES ('SCOTT','BONUS','TABLE');
...
COMMIT;

-- run export DataPump job:

expdp system/password DIRECTORY=my_dir \
DUMPFILE=expdp_s.dmp LOGFILE=expdp_s.log SCHEMAS=scott \
INCLUDE=TABLE:\"IN $SELECT object_name FROM scott.expdp_tab WHERE \ owner=\'SCOTT\' AND object_type=\'TABLE\'$\"

-----------------------

 d、这样的写法是错误的,因为包含两个include语句

    DIRECTORY=ZZW_EXPDPDIR
    DUMPFILE=bdctemp1.dmp
    include=table:"='BOSS'"
    include=table:"='SIMS'"

6 技巧

6.1 不生成文件直接导入目标数据库

在一些情况下，我们并没有足够的存储空间允许我们存储导出的dmp文件。这个时候，我们就无计可施了么？ 不是的。我们可以不生成dmp文件，直接将数据抽取到目标数据。在迁移大量数据而没有充足存储空间时，这是一个救命稻草。 最关键的点就是在目标端执行impdp的时候，使用network_link，直接从源库抽取数据。 示例如下：

cat test.par
directory=dump_dir
logfile=test.log
schemas=test
query="where create_date > last_day(add_months(sysdate,-1)) and create_date <= last_day(sysdate)"
transform=segment_attributes:n
network_link=to_aibcrm
table_exists_action=append
impdp \'\/ as sysdba\' parfile=test.par

6.2 通过shell脚本自动导入

此处只关注，impdp 命令在shell脚本中执行，需要转义的地方。

cat import_sr.sh
#!/bin/sh
cd /u01/app
for da in 2012-10 2013-09 2013-08 2013-07 2013-06 2013-05 2013-04 2013-03 2013-02 2013-01 2012-12 2012-11 2014-08 2014-07 2014-06 2014-05 2014-04 2014-03 2014-02 2014-01 2013-12 2013-11 2013-10 2015-07 2015-06 2015-05 2015-04 2015-03 2015-02 2015-01 2014-12 2014-11 2014-10 2014-09 2016-06 2016-05 2016-04 2016-03 2016-02 2016-01 2015-12 2015-11 2015-10 2015-09 2015-08 2017-05 2017-04 2017-03 2017-02 2017-01 2016-12 2016-11 2016-10 2016-09 2016-08 2016-07;
do
impdp \'\/ as sysdba\' parfile=import_sr.par logfile=sr${da}.log query=\" where create_date\> last_day\(add_months\(to_date\(\'$da\',\'yyyy-mm\'\),-1\)\) and create_date \<\=last_day\(to_date\(\'$da\',\'yyyy-mm\'\)\)\"
done

-- 参数文件内容
directory=dump_dir
tables=SR.SR_VOUCHER_FILE_tomig
remap_table=sr.SR_VOUCHER_FILE_tomig:sr_his.sr_voucher_file
transform=segment_attributes:n
network_link=to_aibcrm
table_exists_action=append

6.3 如何导出数百张表

include=table:"in (select * from &table_name where_clause)"

&table_name  --在表里存储需要导出的表明细