查看: 29974|回复: 29

[Tips] expdp 与 exclude 的问题

[复制链接]
论坛徽章:
189
红宝石
日期:2014-05-09 08:24:37萤石
日期:2014-01-03 10:25:39奥运会纪念徽章:羽毛球
日期:2008-07-01 10:46:06奥运会纪念徽章:马术
日期:2008-07-07 17:43:24奥运会纪念徽章:射箭
日期:2008-07-25 18:07:39奥运会纪念徽章:皮划艇激流回旋
日期:2008-07-30 10:02:57奥运会纪念徽章:花样游泳
日期:2008-09-26 13:02:43奥运会纪念徽章:排球
日期:2008-12-03 11:23:272010新春纪念徽章
日期:2010-01-04 08:33:082010年世界杯参赛球队:澳大利亚
日期:2010-02-26 11:08:44
发表于 2006-7-31 15:31 | 显示全部楼层 |阅读模式
我想使用expdp导出数据,其中有一个表很大,我不想导出,这个命令该如何写:


expdp system/xxx  DIRECTORY=BACKUP DUMPFILE=20060731.dmp LOGFILE=xx.log
EXCLUDE=TABLE:BIGTALE FULL=Y

其中EXCLUDE的参数我使用许多形式,都是出现如下错误:

ORA-39071: Value for EXCLUDE is badly formed.
ORA-00920: invalid relational operator
论坛徽章:
1
2012新春纪念徽章
日期:2012-01-04 11:50:44
发表于 2006-7-31 16:37 | 显示全部楼层
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 examples show how metadata can be filtered with the EXCLUDE and INCLUDE parameters.

1. Syntax of the INCLUDE and EXCLUDE DataPump 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:

expdp <other_parameters> SCHEMAS=scott EXCLUDE=SEQUENCE, TABLE:"IN ('EMP', 'DEPT')"
impdp <other_parameters> SCHEMAS=scott INCLUDE=FUNCTION, PACKAGE, PROCEDURE, 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, procedures, and packages that are owned by SCOTT, and will also import the table EMP (with its dependent objects).

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:

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. Pay special attention when the same filter name for an object type is used 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.

Incorrect syntax (no tables are exported; error: ORA-31655):
INCLUDE=TABLE:"= 'EMP'"
INCLUDE=TABLE:"= 'DEPT'"

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.

Incorrect syntax (error: UDE-00011):
INCLUDE=TABLE:"IN ('EMP', 'DEPT')"
EXCLUDE=INDEX:"= 'PK_EMP'"

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

6. The object types that can be specified, depend on the export/import DataPump 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, DEFAULT_ROLE

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, DEFAULT_ROLE

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, DEFAULT_ROLE
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. Excluding objects during an Export or Import DataPump job.
When specifying the EXCLUDE parameter for en Export DataPump or Import DataPump 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.

9.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 that the following EXCLUDE statements will be interpreted as follows:
- EXCLUDE=CONSTRAINT will exclude all nonreferential constraints, except for NOT NULL constraints and any constraints needed for successful table creation and loading.
- EXCLUDE=REF_CONSTRAINT will exclude referential integrity (foreign key) constraints.

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

9.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.


10. Including objects during an Export or Import DataPump job.
When specifying the INCLUDE parameter for en Export DataPump or Import DataPump 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.

使用道具 举报

回复
论坛徽章:
189
红宝石
日期:2014-05-09 08:24:37萤石
日期:2014-01-03 10:25:39奥运会纪念徽章:羽毛球
日期:2008-07-01 10:46:06奥运会纪念徽章:马术
日期:2008-07-07 17:43:24奥运会纪念徽章:射箭
日期:2008-07-25 18:07:39奥运会纪念徽章:皮划艇激流回旋
日期:2008-07-30 10:02:57奥运会纪念徽章:花样游泳
日期:2008-09-26 13:02:43奥运会纪念徽章:排球
日期:2008-12-03 11:23:272010新春纪念徽章
日期:2010-01-04 08:33:082010年世界杯参赛球队:澳大利亚
日期:2010-02-26 11:08:44
 楼主| 发表于 2006-7-31 17:04 | 显示全部楼层
给出链接我再仔细看看。
谢谢。

使用道具 举报

回复
论坛徽章:
189
红宝石
日期:2014-05-09 08:24:37萤石
日期:2014-01-03 10:25:39奥运会纪念徽章:羽毛球
日期:2008-07-01 10:46:06奥运会纪念徽章:马术
日期:2008-07-07 17:43:24奥运会纪念徽章:射箭
日期:2008-07-25 18:07:39奥运会纪念徽章:皮划艇激流回旋
日期:2008-07-30 10:02:57奥运会纪念徽章:花样游泳
日期:2008-09-26 13:02:43奥运会纪念徽章:排球
日期:2008-12-03 11:23:272010新春纪念徽章
日期:2010-01-04 08:33:082010年世界杯参赛球队:澳大利亚
日期:2010-02-26 11:08:44
 楼主| 发表于 2006-7-31 17:10 | 显示全部楼层
实际上就是命令行打入一堆\

使用道具 举报

回复
论坛徽章:
313
行业板块每日发贴之星
日期:2012-07-12 18:47:29双黄蛋
日期:2011-08-12 17:31:04咸鸭蛋
日期:2011-08-18 15:13:51迷宫蛋
日期:2011-08-18 16:58:25紫蛋头
日期:2011-08-31 10:57:28ITPUB十周年纪念徽章
日期:2011-09-27 16:30:47蜘蛛蛋
日期:2011-10-20 15:51:25迷宫蛋
日期:2011-10-29 11:12:59ITPUB十周年纪念徽章
日期:2011-11-01 16:19:41鲜花蛋
日期:2011-11-09 20:33:30
发表于 2006-7-31 17:43 | 显示全部楼层

这样试试: EXCLUDE=TABLE:\"IN('BIGTALE')\"

我前一段时间想指定表导出,结果使用INCLUDE参数,也是报这个错误;查看文档
他妈的,根据文档的方法,死活都不通,后来看了网上的一个例子,照模画样,
才整出来。写文档的那个家伙,应该被炒鱿鱼。

使用道具 举报

回复
论坛徽章:
189
红宝石
日期:2014-05-09 08:24:37萤石
日期:2014-01-03 10:25:39奥运会纪念徽章:羽毛球
日期:2008-07-01 10:46:06奥运会纪念徽章:马术
日期:2008-07-07 17:43:24奥运会纪念徽章:射箭
日期:2008-07-25 18:07:39奥运会纪念徽章:皮划艇激流回旋
日期:2008-07-30 10:02:57奥运会纪念徽章:花样游泳
日期:2008-09-26 13:02:43奥运会纪念徽章:排球
日期:2008-12-03 11:23:272010新春纪念徽章
日期:2010-01-04 08:33:082010年世界杯参赛球队:澳大利亚
日期:2010-02-26 11:08:44
 楼主| 发表于 2006-8-1 10:51 | 显示全部楼层
就是这样,谢谢!

使用道具 举报

回复
论坛徽章:
9
生肖徽章:马
日期:2006-09-07 17:03:04会员2007贡献徽章
日期:2007-09-26 18:42:10ITPUB9周年纪念徽章
日期:2010-10-08 09:32:27
发表于 2006-8-1 11:17 | 显示全部楼层
学习

使用道具 举报

回复
论坛徽章:
189
红宝石
日期:2014-05-09 08:24:37萤石
日期:2014-01-03 10:25:39奥运会纪念徽章:羽毛球
日期:2008-07-01 10:46:06奥运会纪念徽章:马术
日期:2008-07-07 17:43:24奥运会纪念徽章:射箭
日期:2008-07-25 18:07:39奥运会纪念徽章:皮划艇激流回旋
日期:2008-07-30 10:02:57奥运会纪念徽章:花样游泳
日期:2008-09-26 13:02:43奥运会纪念徽章:排球
日期:2008-12-03 11:23:272010新春纪念徽章
日期:2010-01-04 08:33:082010年世界杯参赛球队:澳大利亚
日期:2010-02-26 11:08:44
 楼主| 发表于 2006-8-1 15:55 | 显示全部楼层

Re: 这样试试: EXCLUDE=TABLE:\"IN('BIGTALE')\"

最初由 ZALBB 发布
[B]我前一段时间想指定表导出,结果使用INCLUDE参数,也是报这个错误;查看文档
他妈的,根据文档的方法,死活都不通,后来看了网上的一个例子,照模画样,
才整出来。写文档的那个家伙,应该被炒鱿鱼。 [/B]


"
,
(
前面都要加\,才可以在linux下。

使用道具 举报

回复
论坛徽章:
15
祖国60周年纪念徽章
日期:2009-10-09 08:28:002009架构师大会纪念徽章
日期:2010-01-18 13:43:21ITPUB十周年纪念徽章
日期:2011-11-01 16:20:28
发表于 2006-8-1 15:59 | 显示全部楼层
haha  对的 嘻嘻

使用道具 举报

回复
论坛徽章:
60
2007年度最佳版主
日期:2008-04-03 16:46:15现任管理团队成员
日期:2011-05-07 01:45:08双黄蛋
日期:2011-06-15 17:03:34ITPUB十周年纪念徽章
日期:2011-11-01 16:19:412012新春纪念徽章
日期:2012-01-04 11:49:542012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:20
发表于 2007-2-4 17:58 | 显示全部楼层
设置一下标签,以便更多人看到

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表