Oracle data pump import encounter bug record
In the afternoon, I received a demand for guided data, a table, more than 200,000 data, and a small case, but encountered a problem.
Key error report:
ORA-39097 ORA-39065 ORA-01427 ORA-39126 ORA-06502 LPX-00225 import statement:
Impdp "'/ as sysdba'" directory=DATA_PUMP_DIR dumpfile=NCHATTAG1.dmp logfile=20180821.log
Log is as follows:
Import: Release 11.2.0.4.0-Production on Tue Aug 21 15:59:30 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
Oracle Database Vault and Real Application Testing options
Master table "SYS". "SYS_IMPORT_FULL_02" successfully loaded/unloaded
Starting "SYS". "SYS_IMPORT_FULL_02": "/ * AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=NCHATTAG1.dmp logfile=20180821.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . Imported "NCHATTAG1". "MESSAGE" 68.48 MB 205729 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role'Punch WANGYPUBG R'does not exist
Failing sql is:
GRANT SELECT ON "NCHATTAG1". MESSAGE "TO" P_WANGYP_R "
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role'Rang NCHATTAG1READER' does not exist
Failing sql is:
GRANT SELECT ON "NCHATTAG1". MESSAGE "TO" R_NCHATTAG1_READER "
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.PUT_DDLS [TABLE_STATISTICS]
ORA-06502: PL/SQL: numeric or value error
LPX-00225: end-element tag "HIST_GRAM_LIST_ITEM" does not match start-element tag "EPVALUE"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 9715
-PL/SQL Call Stack-
Object line object
Handle number name
0x62926f00 21979 package body SYS.KUPW$WORKER
0x62926f00 9742 package body SYS.KUPW$WORKER
0x62926f00 17950 package body SYS.KUPW$WORKER
0x62926f00 4058 package body SYS.KUPW$WORKER
0x62926f00 10450 package body SYS.KUPW$WORKER
0x62926f00 1824 package body SYS.KUPW$WORKER
0x67897468 2 anonymous block
ORA-39097: Data Pump job encountered unexpected error-1427
ORA-39065: unexpected master process exception in DISPATCH
ORA-01427: single-row subquery returns more than one row
Job "SYS". "SYS_IMPORT_FULL_02" stopped due to fatal error at Tue Aug 21 16:00:01 2018 elapsed 0 00:00:30
You can see that the dw process made an error when processing the histogram information while processing the statistics of the table, and there is indeed a histogram on the source-side query table. This kind of internal processing can not be handled correctly, which can be considered as the bug of oracle. The solution is also simple, bypassing statistics:
Impdp "'/ as sysdba'" directory=DATA_PUMP_DIR dumpfile=NCHATTAG1.dmp logfile=20180821.log table_exists_action=replace EXCLUDE=STATISTICS
No error was reported and successfully imported.
Collect statistics manually:
Oracle@bd-dev-oracle-104:/opt/app/oracle/diag/rdbms/bdcfg/BDCFG/trace$ora ant NCHATTAG1 message
\ n=Tue Aug 21 16:54:43 CST 2018 =\ n
Exec DBMS_STATS.GATHER_TABLE_STATS (ownname= > 'NCHATTAG1',tabname= >' message',estimate_percent= > 10 recording invalidates = > false,cascade= > true,degree = > 10)
Can you confirm? [y/n]
After searching on mos, you can refer to the document ID 878626.1
