expdp impdp导入导出指定数据库表

exclude和include参数能够在使用expdp或impdp是对特定的对象或对象类型进行筛选或过滤。比如因工作的需要导出特定的表或不导出特定的表、视图以及存储过程、索引、约束、授权统计信息等等。下面将给出expdp或impdp使用exclude和include参数的方法和示例。

一、exclude/include参数用法：

   EXCLUDE=[object_type]:[name_clause],[object_type]:[name_clause] -->排出特定对象

   INCLUDE=[object_type]:[name_clause],[object_type]:[name_clause] -->包含特定对象

   object_type子句用于指定对象的类型，如table,sequence,view,procedure,package等等

   name_clause子句可以为SQL表达式用于过滤特定的对象名字。它由SQL操作符以及对象名(可使用通配符)来过滤指定对象类型中的特定对象。

   当未指定name_clause而仅仅指定object_type则所有该类型的对象都将被过滤或筛选。多个[object_type]:[name_clause]中间以逗号分割。

   示例：

      expdp <other_parameters> SCHEMAS=scottEXCLUDE=SEQUENCE,TABLE:"IN ('EMP','DEPT')"

      impdp <other_parameters> SCHEMAS=scottINCLUDE=PACKAGE,FUNCTION,PROCEDURE,TABLE:"='EMP'"

 

二、常用的过滤SQL表达式

   EXCLUDE=SEQUENCE,VIEW                         --过滤所有的SEQUENCE,VIEW

   EXCLUDE=TABLE:"IN ('EMP','DEPT')"               --过滤表对象EMP,DEPT

   EXCLUDE=SEQUENCE,VIEW,TABLE:"IN ('EMP','DEPT')" --过滤所有的SEQUENCE,VIEW以及表对象EMP,DEPT

   EXCLUDE=INDEX:"= 'INDX_NAME'"                   --过滤指定的索引对象INDX_NAME

   INCLUDE=PROCEDURE:"LIKE 'PROC_U%'"              --包含以PROC_U开头的所有存储过程(_ 符号代表任意单个字符)

   INCLUDE=TABLE:"> 'E' "                          --包含大于字符E的所有表对象

    其它常用操作符NOTIN,NOT LIKE, <, != 等等

    直接将过滤操作符封装到参数文件中，如下面的例子

    Parameterfile:exp_scott.par

 

   DIRECTORY= dump_scott

    DUMPFILE = exp_scott_%U.dmp

   LOGFILE= exp_scott.log

    SCHEMAS = scott

   PARALLEL=2

   EXCLUDE= TABLE:"IN ('EMP', 'DEPT')"

   

    expdpsystem/manager parfile=exp.par                                                            

 

三、命令行下转义符的处理

Windows平台:

    D:\> expdpsystem/managerDIRECTORY=my_dir DUMPFILE=exp_tab.dmpLOGFILE=exp_tab.log SCHEMAS=scottINCLUDE=TABLE:\"IN ('EMP', 'DEPT')\"

    在Windows平台下，需要对象双引号进行转义，使用转义符\

Unix平台:

    在未使用parfile文件的情形下，所有的符号都需要进行转义，包括括号，双引号，单引号等

    % expdpsystem/managerDIRECTORY=my_dir DUMPFILE=exp_tab.dmpLOGFILE=exp_tab.log SCHEMAS=scottINCLUDE=TABLE:\"IN \'EMP\',\'DEP\'\"

 

四、exclude/include常见的错误

  任意需要转义的字符如果未转义或转义错误，都会产生ORA错误。下面给出几种常见的ORA错误。

    ORA-39001: invalid argument value

    ORA-39071:Valuefor INCLUDEisbadly formed.

    ORA-00936: missing expression

    ORA-39001: invalid argument value

    ORA-39071:Valuefor EXCLUDEisbadly formed.

    ORA-00904: “DEPT”: invalid identifier

    ORA-39001: invalid argument value

    ORA-39041:Filter“INCLUDE” either identifiesallobject typesorno objecttypes.

    ORA-39001: invalid argument value

    ORA-39041:Filter“EXCLUDE” either identifiesallobject typesorno objecttypes

    ORA-39001: invalid argument value

    ORA-39038:Objectpath “USER”isnot supportedforTABLE jobs.

 

五、导出示例

 -->导出不包含sequence，表EMP，DEPT的其它所有对象

    [Oracle@orasrv scott]$ expdp scott/tigerdirectory=dump_scott dumpfile=fliter_1.dmplog=fliter_1.log schemas=scott \

    >exclude=sequence,table:\" in \'EMP\',\'DEPT\'\"

 -->包含大于字符T的所有表对象

    [oracle@orasrv ~]$ expdp scott/tigerdirectory=dump_scott dumpfile=tmp.dmplogfile=tmp.loginclude=table:\"\>\'T\'\"

    ...........

   Estimatein progressusingBLOCKS method...

    Processingobjecttype SCHEMA_EXPORT/TABLE/TABLE_DATA

    Total estimationusingBLOCKS method:18.06 MB

    Processingobjecttype SCHEMA_EXPORT/TABLE/TABLE

    Processingobjecttype SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

    . . exported "SCOTT"."TB_PARL"                          7.020MB   72598rows

    . . exported "SCOTT"."TT"                               7.009MB   72493rows

    . . exported "SCOTT"."XP":"P2"                          5.398KB       1rows

   Mastertable "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

    ******************************************************************************

   Dumpfile setforSCOTT.SYS_EXPORT_SCHEMA_01is:

      /home/oracle/dump/scott/tmp.dmp

    Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completedat14:24:55

 

-->仅仅导入表对象TT，如果存在则将其替换

    [oracle@orasrv scott]$ impdp scott/tigerdirectory=dump_scott dumpfile=tmp.dmplogfile=tmp_imp.log \

    >include=table:\" =\'TT\' \" table_exists_action=replace

    .............

    Connectedto: OracleDatabase11gEnterprise Edition Release11.2.0.1.0 - Production

   Withthe Partitioning, OLAP,DataMining andRealApplication Testing options

   Mastertable "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

    Starting "SCOTT"."SYS_IMPORT_FULL_01":  scott/******** directory=dump_scott dumpfile=tmp.dmp logfile=tmp_imp.log

    include=table:" ='TT' " table_exists_action=replace

    Processing object type SCHEMA_EXPORT/TABLE/TABLE

    Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

    . . imported "SCOTT"."TT"                                7.009 MB   72493 rows

    Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

    Job "SCOTT"."SYS_IMPORT_FULL_01" successfully completed at 14:59:32

 

-->下面是一个实际实际导出过程中的parfile参数,exclude参数用于排出所有包含BAK的表对象,以及一些索引对象,使用了in,like运算符

    userid=goex_admin

    directory=DB_DUMP_DIR

    dumpfile=sybo2_full_%U.dmp

    logfile=sybo2_full.log

    exclude=table:"like '%BAK%'",index:" in ('PK_ACC_POS_CASH_PL_TBL_ARC_11','pk_acc_pos_cash_tbl_arc_11',

     'PK_ACC_POS_STOCK_ARCH_TBL_11','I_ACC_POS_STOCK_TBL_ARC_DT','I_ACC_POS_CASH_PL_ARCH_TBL_DT') ",

     table:"in ('ACC_POS_STOCK_TBL_ARC')"

    filesize=5368709120

    schemas=goex_admin

 

 -->下面是一个使用query查询过滤的情形

  -->该操作导出的schema为scott，表对象emp中deptno=20，以及dept表中deptno=20的记录被导出，这两个表中的其余记录被过滤

  -->该操作除上述过滤以后的所有对象将被一一导出

  expdp scott/tiger dumpfile=scott_filter.dmp logfile=scott_filter.log directory=DB_DUMP_DIR schemas=scott \

  > query=scott.emp:'"where deptno=20"',scott.dept:'"where deptno=20"'