合 Oracle 数据泵(expdp和impdp)排除某个表
Tags： Oracle数据泵expdpimpdp数据泵逻辑导出导入EXCLUDE排除表
- 实验
- How to Exclude Multiple Schemas and Tables in Export or Import (Doc ID 2087052.1)
- APPLIES TO:
- GOAL
- SOLUTION
- REFERENCES
- How to Exclude Duplicate Tables From Some Schemas During a Full DataPump Export (Doc ID 1474932.1)
- APPLIES TO:
- SYMPTOMS
- CHANGES
- CAUSE
- SOLUTION
- REFERENCES
- Export/Import DataPump Parameter TABLES - How to Export and Import Tables Residing in Different Schemas (Doc ID 277905.1)
- APPLIES TO:
- GOAL
- SOLUTION
- Solution 1: Use combination of SCHEMAS and INCLUDE parameters.
- Solution 2: Use multiple Data Pump jobs.
- REFERENCES
- 总结
- 解决办法：
- 其它
实验
环境：LHR和LHR1用户下都有T3表，如下：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | [oracle@lhrora11204 ~]$ expdp \'/ AS SYSDBA\' directory=D1 dumpfile=lhr_%U.dmp EXCLUDE=STATISTICS LOGFILE=expdp_1.log SCHEMAS=LHR1,LHR CLUSTER=N COMPRESSION=ALL parallel=4 FILESIZE=2g REUSE_DUMPFILES=y Export: Release 11.2.0.4.0 - Production on Sat Dec 3 03:08:27 2022 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=D1 dumpfile=lhr_%U.dmp EXCLUDE=STATISTICS LOGFILE=expdp_1.log SCHEMAS=LHR1,LHR CLUSTER=N COMPRESSION=ALL parallel=4 FILESIZE=2g REUSE_DUMPFILES=y Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 40 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 . . exported "LHR1"."T2" 950.1 KB 86961 rows . . exported "LHR"."T3" 950.1 KB 86962 rows . . exported "LHR"."T1" 950.1 KB 86960 rows Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA . . exported "LHR1"."T3" 950.2 KB 86964 rows Processing object type SCHEMA_EXPORT/TABLE/TABLE Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /home/oracle/lhr_01.dmp /home/oracle/lhr_02.dmp /home/oracle/lhr_03.dmp /home/oracle/lhr_04.dmp Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Dec 3 03:08:34 2022 elapsed 0 00:00:07 |
如下，排除T3表，会排除LHR和LHR1这2个用户下的T3表：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | [oracle@lhrora11204 ~]$ expdp \'/ AS SYSDBA\' directory=D1 dumpfile=lhr_%U.dmp EXCLUDE=STATISTICS,TABLE:"in('T3')" LOGFILE=expdp_1.log SCHEMAS=LHR1,LHR CLUSTER=N COMPRESSION=ALL parallel=4 FILESIZE=2g REUSE_DUMPFILES=y Export: Release 11.2.0.4.0 - Production on Sat Dec 3 03:10:41 2022 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=D1 dumpfile=lhr_%U.dmp EXCLUDE=STATISTICS,TABLE:in('T3') LOGFILE=expdp_1.log SCHEMAS=LHR1,LHR CLUSTER=N COMPRESSION=ALL parallel=4 FILESIZE=2g REUSE_DUMPFILES=y Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 20 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 . . exported "LHR"."T1" 950.1 KB 86960 rows . . exported "LHR1"."T2" 950.1 KB 86961 rows Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /home/oracle/lhr_01.dmp /home/oracle/lhr_02.dmp /home/oracle/lhr_03.dmp Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Dec 3 03:10:48 2022 elapsed 0 00:00:07 |
如下写法并不能排除T3表：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | [oracle@lhrora11204 ~]$ expdp \'/ AS SYSDBA\' directory=D1 dumpfile=lhr_%U.dmp EXCLUDE=STATISTICS,TABLE:"in('LHR.T3')" LOGFILE=expdp_1.log SCHEMAS=LHR1,LHR CLUSTER=N COMPRESSION=ALL parallel=4 FILESIZE=2g REUSE_DUMPFILES=y Export: Release 11.2.0.4.0 - Production on Sat Dec 3 03:09:55 2022 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=D1 dumpfile=lhr_%U.dmp EXCLUDE=STATISTICS,TABLE:in('LHR.T3') LOGFILE=expdp_1.log SCHEMAS=LHR1,LHR CLUSTER=N COMPRESSION=ALL parallel=4 FILESIZE=2g REUSE_DUMPFILES=y Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 40 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 . . exported "LHR1"."T2" 950.1 KB 86961 rows . . exported "LHR"."T3" 950.1 KB 86962 rows . . exported "LHR"."T1" 950.1 KB 86960 rows Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA . . exported "LHR1"."T3" 950.2 KB 86964 rows Processing object type SCHEMA_EXPORT/TABLE/TABLE Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /home/oracle/lhr_01.dmp /home/oracle/lhr_02.dmp /home/oracle/lhr_03.dmp /home/oracle/lhr_04.dmp Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Dec 3 03:10:03 2022 elapsed 0 00:00:07 |
解决：只导出元数据，不导出具体数据：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | [oracle@lhrora11204 ~]$ more parfile.par QUERY=LHR.T3:"where 1 <> 1" [oracle@lhrora11204 ~]$ [oracle@lhrora11204 ~]$ expdp \'/ AS SYSDBA\' directory=D1 dumpfile=lhr_%U.dmp EXCLUDE=STATISTICS LOGFILE=expdp_1.log SCHEMAS=LHR1,LHR CLUSTER=N COMPRESSION=ALL parallel=4 FILESIZE=2g REUSE_DUMPFILES=y parfile=parfile.par Export: Release 11.2.0.4.0 - Production on Sat Dec 3 03:35:28 2022 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=D1 dumpfile=lhr_%U.dmp EXCLUDE=STATISTICS LOGFILE=expdp_1.log SCHEMAS=LHR1,LHR CLUSTER=N COMPRESSION=ALL parallel=4 FILESIZE=2g REUSE_DUMPFILES=y parfile=parfile.par Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 40 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 . . exported "LHR"."T1" 950.1 KB 86960 rows . . exported "LHR1"."T2" 950.1 KB 86961 rows . . exported "LHR1"."T3" 950.2 KB 86964 rows Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA . . exported "LHR"."T3" 5.585 KB 0 rows Processing object type SCHEMA_EXPORT/TABLE/TABLE Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /home/oracle/lhr_01.dmp /home/oracle/lhr_02.dmp /home/oracle/lhr_03.dmp /home/oracle/lhr_04.dmp Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Dec 3 03:35:35 2022 elapsed 0 00:00:07 |
使用查询，也会排除所有用户下的T3表：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | vi parfile.par EXCLUDE=STATISTICS,TABLE:"IN (select TABLE_NAME from DBA_TABLES where owner='LHR' AND table_name='T3')" expdp \'/ AS SYSDBA\' directory=D1 dumpfile=lhr_%U.dmp parfile=parfile.par LOGFILE=expdp_1.log SCHEMAS=LHR1,LHR CLUSTER=N COMPRESSION=ALL parallel=4 FILESIZE=2g REUSE_DUMPFILES=y [oracle@lhrora11204 ~]$ expdp \'/ AS SYSDBA\' directory=D1 dumpfile=lhr_%U.dmp parfile=parfile.par LOGFILE=expdp_1.log SCHEMAS=LHR1,LHR CLUSTER=N COMPRESSION=ALL parallel=4 FILESIZE=2g REUSE_DUMPFILES=y Export: Release 11.2.0.4.0 - Production on Sat Dec 3 03:43:09 2022 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=D1 dumpfile=lhr_%U.dmp parfile=parfile.par LOGFILE=expdp_1.log SCHEMAS=LHR1,LHR CLUSTER=N COMPRESSION=ALL parallel=4 FILESIZE=2g REUSE_DUMPFILES=y Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 20 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 . . exported "LHR"."T1" 950.1 KB 86960 rows . . exported "LHR1"."T2" 950.1 KB 86961 rows Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /home/oracle/lhr_01.dmp /home/oracle/lhr_02.dmp /home/oracle/lhr_03.dmp Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Dec 3 03:43:19 2022 elapsed 0 00:00:09 |
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.
- Example to use include on the command line to include all tables for owner AJ or BJ:
1 2 | expdp system/<PASSWORD> full=Y directory=<DIRECTORY_NAME> dumpfile=<DUMP_NAME>.dmp logfile=<LOG_NAME>.log include=table:\"in \(select table_name from dba_tables where owner like \'AJ%\' or owner like \'BJ%\' \)\" |
- 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:
1 2 | EXCLUDE=TABLE:"IN (select table_name from dba_tables where (owner,table_name) in (('<user1>','<table1>'), ('<user2>','<table2>')) )" |
Example:
1 2 | EXCLUDE=TABLE:"IN (select TABLE_NAME from DBA_TABLES where (owner,table_name) in (('AJ','A1_TAB'), ('BJ','B1_TAB')) )" |
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):
1 2 3 | QUERY=<owner1>.<table_name>:"where 1 <> 1" QUERY=<owner2>.<table_name>:"where 1 <> 1" |
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.
