oracle的39126,oracle expdp/impdp ORA-39126错误

weixin_39640195

于 2021-04-08 07:43:03 发布

阅读量2.4k

点赞数

文章标签： oracle的39126

使用dblink用expdp/impdp的network_link从远程导大量表数据时，出现下面的问题：本地数据库是11.1.0.6,远程数据库是10.2.04

一、当用tables指定多个表时，出现以下错误：

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

ORA-39001: invalid argument value

ORA-39071: Value for TABLES is badly formed.

将表的数据减少后正常，原因：

This is due to bug in Oracle <11. When you are using parameter TABLES=, it cannot be longer than 4000 characters. To ommit this you can create table containing list of all the tables you want to export :

CREATE TABLE expdp_tab (owner VARCHAR2(30), object_name VARCHAR2(128), object_type VARCHAR2(19));

INSERT INTO expdp_tab VALUES ('schema','table_name','TABLE');

....

commit;

and then replace TABLES=(…) in parfile with this:

INCLUDE=TABLE:”IN (SELECT OBJECT_NAME FROM EXPDP_TAB)”

二、根据一出现的原因，做以下处理：

1、create table dump_tables as select segment_name

from (select segment_name, rownum rn

from (select segment_name

from user_segments

where partition_name is null

and segment_type = 'TABLE'

order by bytes desc) a) b

where b.rn > 55

2、expdp cdw183_backup/cdw183_backup@cdwrac NETWORK_LINK=cdw183 include=table:"in (select segment_name from dump_tables)" DIRECTORY=cdw183_backup dumpfile=cdw183_backup.dmp

Export: Release 11.1.0.6.0 - 64bit Production on Saturday, 09 February, 2013 15:06:22

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

Starting "CDW183_BACKUP"."SYS_EXPORT_SCHEMA_13": cdw183_backup/********@cdwrac NETWORK_LINK=cdw183 include=table:in (select segment_name from dump_tables) DIRECTORY=cdw183_backup dumpfile=cdw183_backup.dmp

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

ORA-39126: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS []

ORA-00936: missing expression

ORA-06512: at "SYS.DBMS_METADATA", line 1548

ORA-06512: at "SYS.DBMS_METADATA", line 1585

ORA-06512: at "SYS.DBMS_METADATA", line 1892

ORA-06512: at "SYS.DBMS_METADATA", line 3878

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86

ORA-06512: at "SYS.KUPW$WORKER", line 7704

----- PL/SQL Call Stack -----

object line object

handle number name

0x150904910 18051 package body SYS.KUPW$WORKER

0x150904910 7736 package body SYS.KUPW$WORKER

0x150904910 11906 package body SYS.KUPW$WORKER

0x150904910 2255 package body SYS.KUPW$WORKER

0x150904910 8342 package body SYS.KUPW$WORKER

0x150904910 1477 package body SYS.KUPW$WORKER

0x13f115ad0 2 anonymous block