Oracle 数据泵(expdp和impdp)排除某个表

实验

环境：LHR和LHR1用户下都有T3表，如下：

如下，排除T3表，会排除LHR和LHR1这2个用户下的T3表：

如下写法并不能排除T3表：

解决：只导出元数据，不导出具体数据：

使用查询，也会排除所有用户下的T3表：

How to Exclude Multiple Schemas and Tables in Export or Import (Doc ID 2087052.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.1 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.

NOTE: In the document content below, the user information and data used represents fictitious data. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

GOAL

Include or Exclude multiple tables from multiple schemas in full Export or Import.

SOLUTION

Following example will show to exclude or include multiple tables or schemas.
It can be used either on the command line or in a parameter file.

1. Example to use include on the command line to include all tables for owner AJ or BJ:

2. Example to use exclude in a par file to exclude table A1_TAB owned by user AJ and B1_TAB owned by user BJ.

Syntax:

Example:

There is another workaround mentioned in following document by using the QUERY parameter to make sure no rows for these tables are exported (only metadata is exported). Please refer to:
Note 1474932.1 - How to Exclude Duplicate Tables From Some Schemas During a Full DataPump Export

REFERENCES

BUG:6860716 - EXPORT&FILTER TABLES FROM MULTIPLE SCHEMAS IN DATA PUMP
NOTE:341733.1 - Export/Import DataPump Parameters INCLUDE and EXCLUDE - How to Load and Unload Specific Objects

How to Exclude Duplicate Tables From Some Schemas During a Full DataPump Export (Doc ID 1474932.1)

APPLIES TO:

Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.

SYMPTOMS

You need to do a full database export but the same table is present in several schemas and you want to exclude this table for one schemas only.

CHANGES

NONE

CAUSE

Duplicate table names.

SOLUTION

Unfortunately, you cannot exclude the tables. From Note 277905.1:

\1. Note that all specified tables in the selected schemas are exported, together with their dependent objects.
It is not possible to include or exclude tables owned by a specific schema with a full database export DataPump job, or schema level export DataPump job with multiple schema's.

\2. As a workaround, you can use the query parameter to make sure no rows for these tables are exported (only metadata is exported):

REFERENCES

NOTE:277905.1 - Export/Import DataPump Parameter TABLES - How to Export and Import Tables Residing in Different Schemas

Export/Import DataPump Parameter TABLES - How to Export and Import Tables Residing in Different Schemas (Doc ID 277905.1)

APPLIES TO:

Oracle Database - Personal Edition - Version 10.1.0.2 to 11.1.0.7 [Release 10.1 to 11.1]
Oracle Database - Standard Edition - Version 10.1.0.2 to 11.1.0.7 [Release 10.1 to 11.1]
Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.1.0.7 [Release 10.1 to 11.1]
Information in this document applies to any platform.

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product.
Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

GOAL

With the classic export client you were able to export with one single export session, tables that were owned by different users.

For example to export the following tables:
- table EMP owned by user SCOTT
- table EMPLOYEES owned by user HR
- table CATEGORIES_TAB owned by user OE

% exp system/ FILE=.dmp LOG=.log \
TABLES=scott.emp,hr.employees,oe.categories_tab

Export: Release 11.1.0.6.0 - Production on Wed Nov 28 10:04:10 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8ISO8859P15 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
Current user changed to SCOTT
. . exporting table EMP 14 rows exported
Current user changed to HR
. . exporting table EMPLOYEES 107 rows exported
Current user changed to OE
. . exporting table CATEGORIES_TAB 22 rows exported
. . exporting table PRODUCT_REF_LIST_NESTEDTAB 288 rows exported
. . exporting table SUBCATEGORY_REF_LIST_NESTEDTAB 21 rows exported
Export terminated successfully without warnings.

However, with Export Data Pump you get the following error:

% expdp system/ DIRECTORY= \
DUMPFILE=.dmp LOGFILE=.log \
TABLES=scott.emp,hr.employees,oe.categories_tab

Export: Release 11.1.0.6.0 - Production on Wednesday, 28 November, 2007 10:07:13
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
UDE-00012: table mode exports only allow objects from one schema

This is caused by a restriction for the TABLES parameter of Data Pump:
"The table name that you specify can be preceded by a qualifying schema name. All table names specified must reside in the same schema."

An enhancement request has been filed to make the syntax similar to that of the original export client:
Bug 6860716 - EXPORT&FILTER TABLES FROM MULTIPLE SCHEMAS IN DATA PUMP

This is implemented in version 11.2.

How to achieve the same kind of export with Oracle Data Pump ?

SOLUTION

Solution 1: Use combination of SCHEMAS and INCLUDE parameters.

When using Data Pump you can use the combination of the SCHEMAS and INCLUDE parameters.
Example:

Remarks:

\1. Note that all specified tables in the selected schemas are exported, together with their dependent objects. It is not possible to include or exclude tables owned by a specific schema with a full database export Data Pump job, or schema level export Data Pump job with multiple schema's.

\2. Note that if you want to specify these parameters on the command line, you have to use the escape character for special characters:

Example for Windows (place all parameters on a single line):

Example for Unix/Linux (escape all special characters):

\3. For additional details, see also:
Note 341733.1 - Export/Import DataPump Parameters INCLUDE and EXCLUDE - How to Load and Unload Specific Objects

Solution 2: Use multiple Data Pump jobs.

Alternatively, you can also run multiple jobs, e.g.:

-- Export table: scott.emp

-- Export table: hr.employees

-- Export table: oe.categories_tab

Remarks:

\1. Oracle Data Pump restrictions for the TABLES parameter:

- All table names specified must reside in the same schema.
- To specify a schema other than your own, you must have the EXP_FULL_DATABASE role.
- The use of wildcards is supported for one table name per export operation.

\2. For additional details, see also:
Note 277899.1 - Export/Import DataPump: UDE-00014 When Using Wildcard in the TABLES Parameter

\3. Another example if you want to exclude two specific tables (SCOTT.EMP and HR.EMPLOYEES) from a full database export Data Pump job:

-- Run a full database export, excluding all EMP and EMPLOYEES tables:

-- Export tables EMP owned by users other than SCOTT:

-- To export tables EMPLOYEES owned by users other than HR:

REFERENCES

NOTE:341733.1 - Export/Import DataPump Parameters INCLUDE and EXCLUDE - How to Load and Unload Specific Objects
NOTE:351598.1 - Export/Import DataPump: The Minimum Requirements to Use Export DataPump and Import DataPump (System Privileges)

NOTE:277899.1 - Export/Import DataPump: UDE-00014 When Using Wildcard in the TABLES Parameter

总结

数据泵要排除某个表，表前不能加用户名，尽管命令不报错，但是会导致EXCLUDE的TABLE选项排除失效，正确写法，如下

如下，不能排除LHR.T3表，虽然不报错，但是会导致EXCLUDE的TABLE选项排除失效：

如下，会排除T3表，但是会排除掉LHR和LHR1中的所有T3表，

解决办法：

1、使用parfile和QUERY只导出元数据，而不导出某个具体表：

2、若LHR1和LHR用户下，只有LHR用户含T3表，则也可以使用如下的SQL

对于某个表，可以单独进行导出和导入：

其它

数据泵常用写法参考：https://www.xmmup.com/oracleshujubengdaochudaoruchangyongxiefa.html