DataPump Import (impdp) Raises ORA-942 When Importing Statistics (Doc ID 1313200.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1 to 11.2.0.4 [Release 11.2]
Information in this document applies to any platform.
***Checked for relevance on 06-Aug-2014***

Symptoms

You recently upgraded the database from version 10.2 onto 11gR2. Then the DataPump import fails with errors, like:

...
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39083: Object type TABLE_STATISTICS failed to create with error:
ORA-06550: line 1, column 454:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 1, column 434:
PL/SQL: SQL Statement ignored
ORA-06550: line 1, column 489:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 1, column 469:
PL/SQL: SQL Statement ignored
Failing sql is:
DECLARE c varchar2(60); nv varchar2(1); df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; s varchar2(60) := 'TEST'; t varchar2(60) := 'BIG_TA
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 14:38:07

when the statistics are imported.
If you exclude the statistics, then the import works.

DataPump import internally uses the table SYS.IMPDP_STATS. You checked the data dictionary with:

select * from dba_objects where owner = 'SYS' and object_name = 'IMPDP_STATS';

and the table/index exist and are valid:

OWNER
------------------------------
OBJECT_NAME
----------------------------------------------------------------------------
SUBOBJECT_NAME                 OBJECT_ID  DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED              LAST_DDL_TIME        TIMESTAMP           STATUS  T G S
-------------------- -------------------- ------------------- ------- - - -
NAMESPACE   EDITION_NAME
---------- ------------------------------
SYS
IMPDP_STATS
                                    79059                TABLE
13-APR-2011 15:10:27 13-APR-2011 15:10:28 2011-04-13:15:10:27 VALID   Y N N
1

SYS
IMPDP_STATS
                                    79062          79062 INDEX
13-APR-2011 15:10:28 13-APR-2011 15:10:28 2011-04-13:15:10:28   VALID Y N N
4

Changes

Upgraded from 10.2.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms