expdp的一些限制（ZT）

最新推荐文章于 2021-07-20 09:49:52 发布

cq14785

最新推荐文章于 2021-07-20 09:49:52 发布

阅读量980

点赞数

文章标签：数据库

1,语法
expdp help=y
impdp help=y
grant create any directory to xxx ;
grant read ,write on directory dump_pgbb to xxx;
grant exp_full_database to xxx ;
grant imp_full_database to xxx ;

2,参数
parallel
不应该超过CPU数的2倍。
在10.2.0.3 会报ORA-39014 系列错误，解决方案，去掉parallel参数。
network_link
一般expdp的文件都在本地，但是通过network_link参数可以实现把dump文件放在远端的主机上，或者甚至直接利用impdp而不形成文件，但是这个参数要求表内不能有LONG类型字段，而LOB字段则可以。
estimate_only
使用ESTIMATE_ONLY 的时候，不能加 dumpfile 参数，否则会报如下错：
ORA-39002: invalid operation
ORA-39201: Dump files are not supported for estimate only jobs
filesize
使用m单位是可以的，使用g貌似不行
schemas
不能和tables同时使用，如果想导出非登陆schema的tables，可以使用 tables=schema.tables
tables
可以加括号，也可以不加括号，表名要写在一行，表名不需要单引号括起来。
不能放太多表，超过100个会报错，这点从%U的格式是00 2位也能看出来。
UDE-00019: 'tables' parameter list is too long.
dumpfile
可以用%U变量，让系统自动生成文件。
include/exclude
INCLUDE = object_type[:name_clause] [, ...]
To see a list of valid object type path names for use with the INCLUDE parameter, you can query the following views:
DATABASE_EXPORT_OBJECTS, SCHEMA_EXPORT_OBJECTS, and TABLE_EXPORT_OBJECTS.
特别注意要使用转义字符
btw ：include 似乎与tables参数冲突，如果同时指定，会报错ORA-39166: Object TABLES1 was not found 参加下6

1. Syntax of the INCLUDE and EXCLUDE DataPump parameters.
EXCLUDE = object_type[:name_clause] [, ...]
INCLUDE = object_type[:name_clause] [, ...]
Examples:
expdp SCHEMAS=scott EXCLUDE=SEQUENCE, TABLE:"IN ('EMP', 'DEPT')"
impdp SCHEMAS=scott INCLUDE=FUNCTION, PACKAGE, PROCEDURE, TABLE:"= 'EMP'"
2. SQL 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.

参考http://www.itpub.net/602958.html

3,例子

4,报错
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name "DW01" prematurely terminated
ORA-31671: Worker process DW01 had an unhandled exception.
ORA-12801: error signaled in parallel query server P003, instance pgbb_db1:pgbb1 (1)
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-31626: job does not exist
ORA-06512: at "SYS.ORACLE_DATAPUMP", line 19
ORA-06512: at "SYS.KUPW$WORKER", line 1342
ORA-06512: at line 2

网上有几种方案
1，补丁修复
Oracle Server - Enterprise Edition - Version: 10.2.0.3.0
This problem can occur on any platform.
This is Bug 5879865. It is fixed in the 11g release.
Solution
1. When available, download Patch 5879865 to resolve this issue.
Note that this patch was not available at the time of writing this article ({JAN 2008}).
2. In the meantime, please use one of the following as a workaround:
set both of the following in the init/spfile
"_complex_view_merging" = false
event="38066 trace name context forever, level 1" or level 2
OR
set "_optimizer_cost_based_transformation"=off
Any of these workarounds can be done at the system level or set in the init or spfile file.
EXAMPLE:
===============
AT SYSTEM LEVEL:
SQL> alter system set events '38066 trace name context forever, level 2';
SQL> alter system set "_complex_view_merging" = false;
SQL> alter system set "_optimizer_cost_based_transformation"=off
IN THE INIT.ORA:
event="38066 trace name context forever, level 2"
_complex_view_merging=false
_optimizer_cost_based_transformation=off
IN THE SPFILE (requires restart of instance):
SQL> alter system set events '38066 trace name context forever, level 2' scope=spfile;
SQL> alter system set "_complex_view_merging" = false scope=spfile;
SQL> alter system set "_optimizer_cost_based_transformation"=off scope=spfile;
3. Flush the shared pool or restart the database.
SQL>alter system flush shared_pool;
4. Perform. the Data Pump export again.
2，exclude=statistc
3，去掉PARALLEL参数 (已测)