oracle11.2.0.3.0 - 64bit,数据泵导入报ORA-06502，解决

来源：互联网发布：vmware for mac 编辑：程序博客网时间：2024/06/10 23:03

数据泵导入是报ORA-06502错误，

信息如下：

oracle> impdp parfile=impp.par

Import: Release 11.2.0.3.0 - Production on Thu Oct 31 20:54:07 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

Job: CONMETAMMO1
Operation: IMPORT
Mode: FULL
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /oracle/work/peng.dmp

Worker 1 Status:
Process Name: DW00
State: EXECUTING

Worker 2 Status:
State: UNDEFINED
Master table "CONME"."CONMETAMMO1" successfully loaded/unloaded

Job: CONMETAMMO1
Operation: IMPORT
Mode: FULL
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /oracle/work/peng.dmp

Worker 1 Status:
Process Name: DW00
State: EXECUTING

Worker 2 Status:
State: UNDEFINED
Starting "CONME"."CONMETAMMO1": common/******** parfile=imppeng.par

Job: CONMETAMMO1
Operation: IMPORT
Mode: FULL
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /oracle/work/peng.dmp

Worker 1 Status:
Process Name: DW00
State: EXECUTING

Worker 2 Status:
State: UNDEFINED
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Job: CONMETAMMO1
Operation: IMPORT
Mode: FULL
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /oracle/work/peng.dmp

Worker 1 Status:
Process Name: DW00
State: EXECUTING

Worker 2 Status:
State: UNDEFINED
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UPATE_TD_ROW_IMP [16]
TABLE_DATA:"CONME"."CONME.BD_ALL_NOMUSICSPBIZIINFO_BAK1"
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 9007

----- PL/SQL Call Stack -----
object line object
handle number name
700000de3e6bf28 20476 package body SYS.KUPW$WORKER
700000de3e6bf28 9028 package body SYS.KUPW$WORKER
700000de3e6bf28 20038 package body SYS.KUPW$WORKER
700000de3e6bf28 4117 package body SYS.KUPW$WORKER
700000de3e6bf28 9725 package body SYS.KUPW$WORKER
700000de3e6bf28 1775 package body SYS.KUPW$WORKER
700000dc4b8db98 2 anonymous block

Job: CONMETAMMO1
Operation: IMPORT
Mode: FULL
State: STOPPING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 1
Dump File: /oracle/work/peng.dmp

Worker 1 Status:
Process Name: DW00
State: EXECUTING

Worker 2 Status:
Process Name: DW00
State: EXECUTING
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UPATE_TD_ROW_IMP [16]
TABLE_DATA:"CONME"."CONME.BD_ALL_NOMUSICSPBIZIINFO_BAK1"
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 9007

----- PL/SQL Call Stack -----
object line object
handle number name
700000de91af8a8 20476 package body SYS.KUPW$WORKER
700000de91af8a8 9028 package body SYS.KUPW$WORKER
700000de91af8a8 20038 package body SYS.KUPW$WORKER
700000de91af8a8 4117 package body SYS.KUPW$WORKER
700000de91af8a8 9725 package body SYS.KUPW$WORKER
700000de91af8a8 1775 package body SYS.KUPW$WORKER
700000ec17a6138 2 anonymous block

Job: CONMETAMMO1
Operation: IMPORT
Mode: FULL
State: STOPPED
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 1
Dump File: /oracle/work/peng.dmp

Worker 1 Status:
Process Name: DW00
State: EXECUTING

Worker 2 Status:
Process Name: DW00
State: EXECUTING
Job "CONME"."CONMETAMMO1" stopped due to fatal error at 20:54:23

我的impp.par文件内容如下：

userid=*********/**************
DIRECTORY=TRANS_TEST
DUMPFILE=peng.dmp
LOGFILE=pengmemta.log
JOB_NAME=CONMETAMMO1
STATUS=300
CONTENT=DATA_ONLY
REMAP_TABLE=CONME.BD_ALL_NOMUSICSPBIZIINFO:CONME.BD_ALL_NOMUSICSPBIZIINFO_BAK1

在网上查资料，还是报这个错，后来我将impp.par文件的REMAP_TABLE参数改为：

REMAP_TABLE=BD_ALL_NOMUSICSPBIZIINFO:BD_ALL_NOMUSICSPBIZIINFO_BAK1

也就是去掉了属主，这次导入成功，日志如下：

oracle> impdp parfile=imppeng.par

Import: Release 11.2.0.3.0 - Production on Thu Oct 31 20:49:49 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

Job: CONMETAMMO1
Operation: IMPORT
Mode: FULL
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /oracle/work/peng.dmp

Worker 1 Status:
Process Name: DW00
State: EXECUTING
Master table "CONME"."CONMETAMMO1" successfully loaded/unloaded

Job: CONMETAMMO1
Operation: IMPORT
Mode: FULL
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /oracle/work/peng.dmp

Worker 1 Status:
Process Name: DW00
State: EXECUTING
Starting "CONME"."CONMETAMMO1": CONME/******** parfile=imppeng.par

Job: CONMETAMMO1
Operation: IMPORT
Mode: FULL
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /oracle/work/peng.dmp

Worker 1 Status:
Process Name: DW00
State: EXECUTING
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Job: CONMETAMMO1
Operation: IMPORT
Mode: FULL
State: COMPLETING
Bytes Processed: 2,083,528,928
Percent Done: 100
Current Parallelism: 1
Job Error Count: 0
Dump File: /oracle/work/peng.dmp

Worker 1 Status:
Process Name: DW00
State: WORK WAITING
. . imported "CONME"."BD_ALL_NOMUSICSPBIZIINFO_BAK" 1.940 GB 10101591 rows

Job: CONMETAMMO1
Operation: IMPORT
Mode: FULL
State: COMPLETED
Bytes Processed: 2,083,528,928
Percent Done: 100
Current Parallelism: 1
Job Error Count: 0
Dump File: /oracle/work/peng.dmp

Worker 1 Status:
Process Name: DW00
State: WORK WAITING
Job "CONME"."CONMETAMMO1" successfully completed at 20:50:14

从这个报错可看出是由于REMAP_TABLE参数设置的太长导致的错误