DataPump Import (IMPDP) Fails With Errors ORA-39083 ORA-907 ORA-39125 LPX-00230 When Importing Views (Doc ID 803043.1)

To BottomTo Bottom

In this Document

  Symptoms
  Changes
  Cause
  Solution
  References

 

APPLIES TO: 

Oracle Database - Enterprise Edition - Version 10.2.0.4 to 11.1.0.7 [Release 10.2 to 11.1]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.

SYMPTOMS

You run DataPump export/import between two databases with multibyte character sets (for example AL32UTF8). DataPump export was successful.

During import you received errors like:

Processing object type SCHEMA_EXPORT/VIEW/VIEW 
ORA-39083: Object type VIEW failed to create with error: 
ORA-907: missing right parenthesis 
Failing sql is: 
CREATE FORCE VIEW ...
ORA-39125: Worker unexpected fatal error in KUPW$WORKER.PUT_DDLS while calling DBMS_METADATA.CONVERT [VIEW:"<OWNER>"."<VIEW_NAME>"] 
ORA-6502: PL/SQL: numeric or value error 
LPX-00230: invalid character 32 (U+0020) found in a Name or Nmtoken 
ORA-6512: at "SYS.DBMS_SYS_ERROR", line 105 
ORA-6512: at "SYS.KUPW$WORKER", line 6313 


The issue is triggered by the multibyte characters comments used in creation statements of views/materialized views reported in the import log.

CHANGES

 

CAUSE

The issue was investigated in 
  Bug 7700493 - IMPDP FAILS WITH ORA-39083 ORA-907 ORA-39125 LPX-00230 WHEN IMPORTING VIEWS
closed as duplicate of unpublished
  Bug 7277077 - A LPX-230 ERROR OCCURS WHEN IMPORTING A MATERIALIZED VIEW WITH MANY COMMENTS, fixed in 11.2.

SOLUTION

1. Upgrade to 11.2.

- OR -

2. Install Patch 7277077, if available for your platform and RDBMS version.

For releases for which one-off patches are not available please open a backport request with Oracle Support.

Note:
The patch needs to applied on both source and target databases.


- OR -

3. Use the following workarounds: 
 

  • Perform impdp with EXCLUDE=VIEW or/and MATERIALIZED_VIEW parameter. 
    This will create all the objects except the views/materialized views. 
    Recreate the views and/or materialized views in target database after the import using the creation scripts from source database or from file generated by impdp used with SQLFILE parameter.
     
  • Use traditional export/import, exp/imp.

REFERENCES


BUG:7700493 - IMPDP FAILS WITH ORA-39083 ORA-00907 ORA-39125 LPX-00230 WHEN IMPORTING VIEWS