ITPUX技术网

交流 . 资讯 . 分享
Make progress together!
Oracle数据库DBA高级工程师培训视频
Oracle数据库培训-备份恢复-性能优化-集群容灾
mysql数据库视频教程

Oracle数据库导入导出Export/Import DataPump Parameters INCLUDE and EXCLUDE

内容发布:风哥| 发布时间:2016-3-26 09:22:25
oracle数据库导入导出Export/Import DataPump Parameters INCLUDE and EXCLUDE

GOAL
This document demonstrates how to load and unload certain objects with the Oracle10g, Oracle11g, and Oracle12c Export DataPump and Import DataPump utilities. This so-called 'Metadata filtering' is implemented through the EXCLUDE and INCLUDE parameters.
Incorrect usage of metadata filters, can result in errors such as:
ORA-39001: invalid argument value
ORA-39071: Value for INCLUDE is badly formed.
ORA-00936: missing expression

ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00920: invalid relational operator

ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00904: "DEPT": invalid identifier

ORA-39001: invalid argument value
ORA-39041: Filter "INCLUDE" either identifies all object types or no object types.

ORA-39001: invalid argument value
ORA-39041: Filter "EXCLUDE" either identifies all object types or no object types.

ORA-39001: invalid argument value
ORA-39038: Object path "USER" is not supported for TABLE jobs.

UDE-00011: parameter include is incompatible with parameter exclude
ksh: syntax error: '(' unexpected
ORA-39165: Schema <schema_name> was not found.
ORA-39168: Object path <path_name> was not found.

ORA-31655: no data or metadata objects selected for job


SOLUTION
The examples below are based on:
- the demo schema SCOTT that is created with script: $ORACLE_HOME/rdbms/admin/scott.sql
- the directory object my_dir that refers to an existing directory on the server where the Oracle RDBMS is installed. Example:
-- Windows:

CONNECT system/manager
CREATE OR REPLACE DIRECTORY my_dir AS 'D:\export';
GRANT read,write ON DIRECTORY my_dir TO public;

-- Unix:

CONNECT system/manager
CREATE OR REPLACE DIRECTORY my_dir AS '/home/users/export';
GRANT read,write ON DIRECTORY my_dir TO public;
Note that when creating an export DataPump dumpfile, you have to ensure that the filename does not already exist in that directory.
The following sections clarify how metadata can be filtered with the EXCLUDE and INCLUDE parameters.


1. Syntax of the INCLUDE and EXCLUDE Data Pump parameters.
With Metadata filters you can specify a set of objects to be included or excluded from an Export or Import operation, such as tables, indexes, grants, procedures.
EXCLUDE = object_type[:name_clause] [, ...]
INCLUDE = object_type[:name_clause] [, ...]

Examples:

D:\> expdp ... SCHEMAS=scott EXCLUDE=SEQUENCE, TABLE:\"IN ('EMP', 'DEPT')\"
D:\> impdp ... SCHEMAS=scott INCLUDE=FUNCTION, PACKAGE, TABLE:\"= 'EMP'\"
The name_clause (specified after the colon separator) is optional. It allows a selection of specific objects within an object type. The EXCLUDE example above will export the complete SCOTT schema, except (1) the sequences owned by SCOTT and (2) the tables EMP and DEPT (with their dependent objects).
The INCLUDE example above will only import the functions, and packages (no procedures!) that are owned by SCOTT, and will also import the table EMP (with its dependent objects).
Note that if you forget to place the escape characters on the command line, and have spaces in the EXCLUDE and/or INCLUDE parameters, that the job may fail with an error such as: ORA-39071 (Value for EXCLUDE is badly formed).
Be careful with the syntax:
If you put a spaces between the colon and the word IN and the close parenthesis and the double quote, the syntax won't be accepted.

#-- THIS EXCLUDE FAILS:
exclude=schema: "IN ('SYSTEM','PATROL','SYSMAN') "

#--THIS EXCLUDE WORKS
exclude=schema:"IN ('SYSTEM','PATROL','SYSMAN')"
A different kind of filtering is Data filtering. Data filtering is implemented through the QUERY and SAMPLE parameters, which specify restrictions on the table rows that are to be exported. For details, see also:
Note 277010.1 "Export/Import DataPump Parameter QUERY - How to Specify a Query"


2. SQL Operator usage.
The name_clause is a SQL expression that is used as a filter on the object names of the object. It consists of a SQL operator and the values against which the object names of the specified type are to be compared. If no name_clause is provided, all objects of the specified type are excluded/included. The name clause must be separated from the object type with a colon. Examples of operator-usage (when used in a parameter file):
EXCLUDE=SEQUENCE

or:

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

or:

EXCLUDE=INDEX:"= 'MY_INDX'"

or:

INCLUDE=PROCEDURE:"LIKE 'MY_PROC_%'"

or:

INCLUDE=TABLE:"> 'E'"


3. Double quotes and single quotes usage.
The name clause is separated from the object type with a colon. The name clause must be enclosed in double quotation marks. The single-quotation marks are required to delimit the name strings. Using the INCLUDE or EXCLUDE parameter in a parameter file is the preferred method.
Parameter file: exp.par
-------------------------
DIRECTORY = my_dir
DUMPFILE  = exp_tab.dmp
LOGFILE   = exp_tab.log
SCHEMAS   = scott
INCLUDE   = TABLE:"IN ('EMP', 'DEPT')"

> expdp system/manager parfile=exp.par
To run this job without a parameter file, you need to escape the special characters. Incorrect escaping can result in errors such as: ksh: syntax error: '(' unexpected.
Command line examples (for Windows: type parameters on one single line) :
Windows:

D:\> expdp system/manager DIRECTORY=my_dir DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott INCLUDE=TABLE:\"IN ('EMP', 'DEP')\"

Unix:

% expdp system/manager DIRECTORY=my_dir DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott INCLUDE=TABLE:\"IN \(\'EMP\', \'DEP\'\)\"


4. Using the same filter name for an object type more than once.
If multiple filters are specified for an object type, an implicit AND operation is applied to them. That is, the objects that are exported or imported during the job have passed all of the filters applied to their object types.
Parameter file - incorrect syntax (no tables are exported; ORA-31655: no data or metadata objects selected for job):

INCLUDE=TABLE:"='EMP'"  
INCLUDE=TABLE:"='DEPT'"

Parameter file - correct syntax:

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

or (all tables that have an 'E' and a 'P' in their name):

INCLUDE=TABLE:"LIKE '%E%'"
INCLUDE=TABLE:"LIKE '%P%'"


5. The EXCLUDE and INCLUDE parameters are mutually exclusive.
It is not possible to specify both the INCLUDE parameter and the EXCLUDE parameter in the same job.
Parameter file - incorrect syntax (error: UDE-00011):

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

Parameter file - correct syntax:

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


6. Specified object types depend on the export/import Data Pump mode.
During a TABLE level export/import, certain object types that are directly related to SCHEMA or DATABASE level jobs, cannot be specified. The same applies to a SCHEMA level export/import where no DATABASE level object types can be specified.
Example (incorrect spelling of object type USERS (should be: USER); error: ORA-39041):

DIRECTORY = my_dir
DUMPFILE  = exp_tab.dmp
LOGFILE   = exp_tab.log
TABLES    = scott.emp
INCLUDE   = USERS:"= 'SCOTT'", TABLESPACE_QUOTA, SYSTEM_GRANT, ROLE_GRANT

Example (incorrect usage of object types in INCLUDE parameter for a TABLE level export; error: ORA-39038):

DIRECTORY = my_dir
DUMPFILE  = exp_tab.dmp
LOGFILE   = exp_tab.log
TABLES    = scott.emp
INCLUDE   = USER:"= 'SCOTT'", TABLESPACE_QUOTA, SYSTEM_GRANT, ROLE_GRANT

Corrected parameters (run job in schema mode):

DIRECTORY = my_dir
DUMPFILE  = exp_tab.dmp
LOGFILE   = exp_tab.log
SCHEMAS   = scott
INCLUDE   = USER:"= 'SCOTT'", TABLESPACE_QUOTA, SYSTEM_GRANT, ROLE_GRANT
INCLUDE   = TABLE:"= 'EMP'"
To determine the name of the object types can be specified with EXCLUDE and INCLUDE, you can run the following query:
SET lines 200 pages 20000  
COL object_path FOR a60  
COL comments FOR a110  

-- for database level export/import:  
SELECT named, object_path, comments  
  FROM database_export_objects  
WHERE object_path NOT LIKE '%/%';  

-- for table schema export/import:  
SELECT named, object_path, comments  
  FROM schema_export_objects  
WHERE object_path NOT LIKE '%/%';  

-- for table level export/import:  
SELECT named, object_path, comments  
  FROM table_export_objects  
WHERE object_path NOT LIKE '%/%';


7. Only specific object types can be named with a Name clause.
The name clause applies only to object types whose instances have names (for example, it is applicable to TABLE, but not to GRANT).
To determine which object types can be named, you can run the following query:
SET lines 150 pages 20000  
COL object_path FOR a30  
COL comments FOR a110  

-- for database level export/import:  
SELECT named, object_path, comments  
  FROM database_export_objects  
WHERE named='Y';  

-- for table schema export/import:  
SELECT named, object_path, comments  
  FROM schema_export_objects  
WHERE named='Y';  

-- for table level export/import:  
SELECT named, object_path, comments  
  FROM table_export_objects  
WHERE named='Y';  

N OBJECT_PATH                  COMMENTS  
- ---------------------------- -----------------------------------------------
Y CONSTRAINT                   Constraints (including referential constraints)
Y INDEX                        Indexes
Y PROCDEPOBJ                   Instance procedural objects
Y REF_CONSTRAINT               Referential constraints
Y TRIGGER                      Triggers on the selected tables
Note that the object type TABLE is not listed here because this is the query output of the TABLE_EXPORT_OBJECTS view: the tables are already specified with the TABLES parameter in the DataPump job.

Import DataPump example:
DIRECTORY = my_dir  
DUMPFILE  = exp_tab.dmp  
LOGFILE   = exp_tab.log  
TABLES    = scott.emp  
EXCLUDE   = TRIGGER:"IN ('TRIG1', 'TRIG2')", INDEX:"= 'INDX1'", REF_CONSTRAINT


8. Excluding/Including an object, will also exclude/include it's dependent objects.
Dependent objects of an identified object are processed along with the identified object. For example, if a filter specifies that an index is to be included in an operation, then statistics from that index will also be included. Likewise, if a table is excluded by a filter, then indexes, constraints, grants, and triggers upon the table will also be excluded by the filter.

To determine which objects are dependent, e.g. for a TABLE, you can run the following query (in Oracle10g Release 2 and higher):
SET lines 200 pages 20000  
COL object_path FOR a60  
COL comments FOR a110  

-- for TABLE dependent object types (10.2.0.x only):  
SELECT named, object_path, comments  
  FROM database_export_objects  
WHERE object_path LIKE 'TABLE/%';  

N OBJECT_PATH                                 COMMENTS
- ------------------------------------------- ------------------------------------------------
  TABLE/AUDIT_OBJ                             Object audits on the selected tables
  TABLE/COMMENT                               Table and column comments on the selected tables
  TABLE/CONSTRAINT                            Constraints (including referential constraints)
  TABLE/CONSTRAINT/REF_CONSTRAINT             Referential constraints
  TABLE/FGA_POLICY                            Fine-grained auditing policies
  TABLE/GRANT                                 Object grants on the selected tables
  TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT        Object grants on the selected tables
  TABLE/INDEX                                 Indexes
  TABLE/INDEX/STATISTICS                      Precomputed statistics
  TABLE/INSTANCE_CALLOUT                      Instance callouts
  TABLE/MATERIALIZED_VIEW_LOG                 Materialized view logs
  TABLE/POST_INSTANCE/GRANT/PROCDEPOBJ_GRANT  Grants on instance procedural objects
  TABLE/POST_INSTANCE/PROCDEPOBJ              Instance procedural objects
  TABLE/POST_INSTANCE/PROCDEPOBJ_AUDIT        Audits on instance procedural objects
  TABLE/POST_TABLE_ACTION                     Post-table actions
  TABLE/PRE_TABLE_ACTION                      Pre-table actions
  TABLE/PROCACT_INSTANCE                      Instance procedural actions
  TABLE/RLS_CONTEXT                           Fine-grained access control contexts
  TABLE/RLS_GROUP                             Fine-grained access control policy groups
  TABLE/RLS_POLICY                            Fine-grained access control policies
  TABLE/TRIGGER                               Triggers


9. 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/tiger
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/manager 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\'\)\"
Note that this workaround is not applicable for a long list of tablespace names. In Oracle10g Release 2 an exceed of the 4000 bytes for table names or tablespace names could result in UDE-00019 (xxx parameter list is too long). This has been resolved with the fix for Bug:5714205 "EXPDP / IMPDP do not support long lists for parameters" (fixed in 10.2.0.4.0 and 11.1.0.x) where the issue with the limitation has been removed completely.


10. Other issues when excluding objects at a Data Pump job.
When specifying the EXCLUDE parameter for en Export Data Pump or Import Data Pump job, all object types for the given mode of export/import (like schema mode) will be included, except those specified in an EXCLUDE statement. If an object is excluded, all of its dependent objects are also excluded. For example, excluding a table will also exclude all indexes and triggers on the table.

10.1. Excluding Constraints.
The following constraints cannot be excluded:
- NOT NULL constraints.
- Constraints needed for the table to be created and loaded successfully (for example, primary key constraints for index-organized tables or REF SCOPE and WITH ROWID constraints for tables with REF columns).

This means:
-- Exclude all nonreferential constraints, except for
-- NOT NULL constraints and any constraints needed for
-- successful table creation and loading:

EXCLUDE=CONSTRAINT
-- Exclude referential integrity (foreign key) constraints:

EXCLUDE=REF_CONSTRAINT

10.2. Excluding Grants.
Specifying EXCLUDE=GRANT excludes object grants on all object types and system privilege grants.

10.3. Excluding Users.
Specifying EXCLUDE=USER excludes only the definitions of users, not the objects contained within users' schemas. To exclude a specific user and all objects of that user, specify a filter such as the following (where SCOTT is the schema name of the user you want to exclude):
EXCLUDE=SCHEMA:"='SCOTT'"
If you try to exclude a user by using a statement such as EXCLUDE=USER:"= 'SCOTT'", only the CREATE USER scott DDL statement will be excluded, and you may not get the results you expect.


11. Other issues when including objects at a Data Pump job.
When specifying the INCLUDE parameter for an Export Data Pump or Import Data Pump job, only object types explicitly specified in INCLUDE statements (and their dependent objects) are exported/imported. No other object types, such as the schema definition information that is normally part of a schema-mode export when you have the EXP_FULL_DATABASE role, are exported/imported.





上一篇:让Oracle的 SHOW PARAMETER 命令显示隐藏参数
下一篇:oracle重建控制文件
189070296,150201289

专业提供Oracle数据库服务、主机、存储、备份、中间件等相关技术支持服务,QQ号:176140749
关注ITPUX技术网微信公众号itpux_com  ,了解本站最新技术资料的分享.

欢迎加QQ群,提供超多高质量Oracle/Unix/Linux技术文档与视频教程的下载。

Oracle/MySQL/Linux群4-5:189070296  150201289  
Oracle/MySQL/Linux群6-8:244609803   522261684   522651731
备注:请勿重复加群,另请注明 from itpux

加群分享视频教程部分如下:

1、公开课视频:Oracle/MySQL数据库工程师职业发展前景讲解(免费)
http://edu.51cto.com/course/7015.html

2、51CTO学院Oracle数据库高级工程师培训(高薪就业.课程介绍)
http://edu.51cto.com/px/train/131?xiaotu

3、Oracle DBA数据库高级工程师培训视频课程1.1(系列78套+七大阶段+上千案例)
套餐视频地址: http://edu.51cto.com/topic/1121.html

4、MySQL数据库(终身门徒)套餐:http://edu.51cto.com/sd/1e1a6

回复

使用道具 举报

内容发布:oubs| 发布时间:2018-1-15 16:00:14
学习了!!!谢谢!!!
回复 支持 反对

使用道具 举报

1框架
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

快速回复 返回顶部 返回列表