EXPDP 指定排除某些表

使用逻辑工具的EXCLUDE选项可以指定那类不被导出，EXPDP工具的前身EXP如果想要完成同样的任务非常的困难。我们以部分表为例看一下这个带给我们的便利。

如果在命令行中完成备份，特殊字符的转义需要特别注意（我这里使用的是Linux操作系统）。

该用户下有四张表

yhzx@MYTEST>select * from cat;

TABLE_NAME                                                     TABLE_TYPE

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

T1_SAFE                                                         TABLE

TTT                                                                    TABLE

T_PLATFORM_USER                                           TABLE

T_PLATFORM_USER_SAFE                                TABLE

UID_SEQ                                                         SEQUENCE

USERCODE_SEQ                                                    SEQUENCE

6 rows selected.

全部备份：

[oracle@wg2 app]$ expdp yhzx/yhzx@mytest directory=exp dumpfile=yhzx.dmp logfile=yhzx.log

Export: Release 11.2.0.1.0 - Production on Fri Dec 11 14:28:24 2015

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 "YHZX"."SYS_EXPORT_SCHEMA_01":  yhzx/********@mytest directory=exp dumpfile=yhzx.dmp logfile=yhzx.log

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 6 MB

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

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/STATISTICS/TABLE_STATISTICS

. . exported "YHZX"."TTT"                                941.6 KB    4838 rows

. . exported "YHZX"."T_PLATFORM_USER"                    941.6 KB    4838 rows

. . exported "YHZX"."T1_SAFE"                            710.4 KB    4912 rows

. . exported "YHZX"."T_PLATFORM_USER_SAFE"               710.4 KB    4912 rows

Master table "YHZX"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

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

Dump file set for YHZX.SYS_EXPORT_SCHEMA_01 is:

  /u01/app/yhzx.dmp

Job "YHZX"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:28:57

排除TTT表

[oracle@wg2 app]$ expdp yhzx/yhzx@mytest directory=exp dumpfile=yhzx-ttt.dmp logfile=yhzx-ttt.log  EXCLUDE=TABLE:\"IN\(\'TTT\'\)\"

Export: Release 11.2.0.1.0 - Production on Fri Dec 11 14:31:38 2015

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 "YHZX"."SYS_EXPORT_SCHEMA_01":  yhzx/********@mytest directory=exp dumpfile=yhzx-ttt.dmp logfile=yhzx-ttt.log EXCLUDE=TABLE:"IN('TTT')"

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 4 MB

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

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/STATISTICS/TABLE_STATISTICS

. . exported "YHZX"."T_PLATFORM_USER"                    941.6 KB    4838 rows

. . exported "YHZX"."T1_SAFE"                            710.4 KB    4912 rows

. . exported "YHZX"."T_PLATFORM_USER_SAFE"               710.4 KB    4912 rows

Master table "YHZX"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

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

Dump file set for YHZX.SYS_EXPORT_SCHEMA_01 is:

  /u01/app/yhzx-ttt.dmp

Job "YHZX"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:32:04

排除TTT表和T1_SAFE表（排除多表）

[oracle@wg2 app]$ expdp yhzx/yhzx@mytest directory=exp dumpfile=yhzx-ttt-safe.dmp logfile=yhzx-ttt-safe.log  EXCLUDE=TABLE:\"IN\(\'TTT\',\'T1_SAFE\'\)\"

Export: Release 11.2.0.1.0 - Production on Fri Dec 11 14:38:40 2015

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 "YHZX"."SYS_EXPORT_SCHEMA_01":  yhzx/********@mytest directory=exp dumpfile=yhzx-ttt-safe.dmp logfile=yhzx-ttt-safe.log EXCLUDE=TABLE:"IN('TTT','T1_SAFE')"

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 3 MB

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

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/STATISTICS/TABLE_STATISTICS

. . exported "YHZX"."T_PLATFORM_USER"                    941.6 KB    4838 rows

. . exported "YHZX"."T_PLATFORM_USER_SAFE"               710.4 KB    4912 rows

Master table "YHZX"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

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

Dump file set for YHZX.SYS_EXPORT_SCHEMA_01 is:

  /u01/app/yhzx-ttt-safe.dmp

Job "YHZX"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:39:04

备份多用户，排除多个表

下边，用system备份了yhzx、scott两个用户，排除了yhzx下的TTT和T1_SAFE两张表

[oracle@wg2 app]$ expdp system/oracle@mytest directory=exp schemas=yhzx,scott  dumpfile=ss-yhzx-ttt-safe.dmp logfile=ss-yhzx-ttt-safe.log  EXCLUDE=TABLE:\"IN\(\'TTT\',\'T1_SAFE\'\)\"

Export: Release 11.2.0.1.0 - Production on Fri Dec 11 14:43:54 2015

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 "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@mytest directory=exp schemas=yhzx,scott dumpfile=ss-yhzx-ttt-safe.dmp logfile=ss-yhzx-ttt-safe.log EXCLUDE=TABLE:"IN('TTT','T1_SAFE')"

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 3.187 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/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

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/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "YHZX"."T_PLATFORM_USER"                    941.6 KB    4838 rows

. . exported "YHZX"."T_PLATFORM_USER_SAFE"               710.4 KB    4912 rows

. . exported "SCOTT"."DEPT"                              5.937 KB       4 rows

. . exported "SCOTT"."EMP"                               8.570 KB      14 rows

. . exported "SCOTT"."SALGRADE"                          5.867 KB       5 rows

. . exported "SCOTT"."BONUS"                                 0 KB       0 rows

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

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

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:

  /u01/app/ss-yhzx-ttt-safe.dmp

Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:44:24

-- 导出yhzx用户下排除T_PLATFORM开头的所有表：

[oracle@wg2 app]$ expdp yhzx/yhzx@mytest directory=exp  dumpfile=LIKE_TP.dmp logfile=LIKE_TP.log  EXCLUDE=TABLE:\"LIKE\'T_PLATFORM%\'\"


Export: Release 11.2.0.1.0 - Production on Fri Dec 11 15:06:23 2015


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 "YHZX"."SYS_EXPORT_SCHEMA_01":  yhzx/********@mytest directory=exp dumpfile=LIKE_TP.dmp logfile=LIKE_TP.log EXCLUDE=TABLE:"LIKE'T_PLATFORM%'" 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 3 MB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
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
. . exported "YHZX"."TTT"                                941.6 KB    4838 rows
. . exported "YHZX"."T1_SAFE"                            710.4 KB    4912 rows
Master table "YHZX"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for YHZX.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/LIKE_TP.dmp
Job "YHZX"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:06:46

备份多用户，排除T_PLATFORM开头的所有表

 

[oracle@wg2 app]$ expdp system/oracle@mytest directory=exp  schemas=yhzx,scott dumpfile=TP.dmp logfile=TP.log  EXCLUDE=TABLE:\"LIKE\'T_PLATFORM%\'\"


Export: Release 11.2.0.1.0 - Production on Fri Dec 11 15:09:34 2015


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 "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@mytest directory=exp schemas=yhzx,scott dumpfile=TP.dmp logfile=TP.log EXCLUDE=TABLE:"LIKE'T_PLATFORM%'" 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 3.187 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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "YHZX"."TTT"                                941.6 KB    4838 rows
. . exported "YHZX"."T1_SAFE"                            710.4 KB    4912 rows
. . exported "SCOTT"."DEPT"                              5.937 KB       4 rows
. . exported "SCOTT"."EMP"                               8.570 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          5.867 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/TP.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:09:56

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29893219/viewspace-1876907/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29893219/viewspace-1876907/