Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.