ORACLE执行impdp报错---ORA-39126、ORA-06502、LPX-00285、ORA-06512

从生产环境取dmp包后，执行impdp操作导入另一oracle库中

impdp backup/*** directory=NEWBKUP dumpfile=newbkup_abibcore1_76.145.17.67.dmp logfile=newbkup_abibcore1_76.145.17.67imp.log schemas=CMP,CMP_USER,CORPOR,CORPOR_USER,EBMS_USER

报错信息如下：

Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE_BODY

ORA-39126：Worker unexpected fatal error in KUPW$WORKER.PUT_DDLS [PACKAGE_BODY:"CORPOR"."PCKG_EBMS_SETOPAYSPECIAL"]

ORA-06502：PL/SQL：numeric or value error

LPX-00285：invalid Unicode surrogate 0xD8ED 0x4F

ORA-06512：at "SYS.DBMS_SYS_ERROR", line 95

ORA-06512：at "SYS>KUPW$WORKER", line 9001

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

  object     line  object

  handle  number   name

0x9af3b6a50   20462   package body SYS.KUPW$WORKER

......

......

 

ORA-39097：Data Pump job encountered error -1427

ORA-39065：unexpected master process exception in DISPATCH

ORA-01427：single-row subquery returns more than one row

Job "BACKUP"."SYS_IMPORT_SCHEMA_03" stopped due to fatal error at 18:35:21

经分析，从生产环境导出的dmp中有存储过程sql语句，该存储过程放在package中，在执行impdp的时候，会执行该存储过程，由于该执行的过过程中报了编码错误，导致存储过程执行中断。

解决办法：

1、首先清除掉已经导入库中的数据（删用户即可）

drop user 用户名 cascade

 

2、将数据的导入与存储过程的导入分离，先导入数据，之后从生产环境导出存储过程（前提是要解决编码问题）

 

impdp backup/*** directory=NEWBKUP dumpfile=newbkup_abibcore1_76.145.17.67.dmp logfile=newbkup_abibcore1_76.145.17.67imp.log schemas=CMP,CMP_USER,CORPOR,CORPOR_USER,EBMS_USER exclude=package