Datapump Import Fails on Tables With Extended Statistics

Quick post before I leave on vacation. We used Datapump to import a schema from an 11.2 HP-UX database to a 19c Linux database and got errors on a few tables like these:

ORA-39083: Object type TABLE:"MYSCHEMA"."TEST" failed to create with error:
ORA-00904: "SYS_STU0S46GP2UUQY#45F$7UBFFCM": invalid identifier

Failing sql is:
ALTER TABLE "MYSCHEMA"."TEST"  MODIFY ("SYS_STU0S46GP2UUQY#45F$7UBFFCM" NUMBER GENERATED
ALWAYS AS (SYS_OP_COMBINED_HASH("COL1","COL2","COL3")) VIRTUAL )

Workaround was to create the table first empty with no indexes, constraints, etc. and import. Today I was trying to figure out why this happened. Apparently, the table has extended statistics on the three primary key columns. I found a post by Jonathan Lewis that shows a virtual column like the one this table has with extended statistics. The error is on the datapump import, impdp, of the table that has extended statistics. This error is similar to some Oracle documented issues such as:

DataPump Import (IMPDP) Raises The Errors ORA-39083 ORA-904 Due To Virtual Columns Dependent On A Function (Doc ID 1271176.1)

But I could not immediately find something that says that extended statistics cause a table to not be importable using Datapump impdp.

If you want to recreate the problem, try added extended stats like this (which I derived from Jonathan Lewis’s post):

select dbms_stats.create_extended_stats(NULL,'TEST','(COL1, COL2, COL3)') name from dual;

select * from user_tab_cols where table_name='TEST';

Then export table from 11.2 and import to 19c database using datapump. Anyway, posting here for my own memory and in case others find it useful. Maybe this is a bug?

Bobby

1/8/2020 update

Simplified my test case table to this:

CREATE TABLE test
(
  COL1 NUMBER,
  COL2 NUMBER
)
;

select dbms_stats.create_extended_stats(NULL,'TEST','(COL1, COL2)') name from dual;

Datapump export of this table from 11.2.0.4 fails on import with ORA-00904 on 18c and 19c but not 12.2.

1/9/20

Oracle support verified that there are a couple of bugs already out there for this.

1/13/20

You only get this error if you use the SCHEMAS parameter in Datapump. It works if you use the TABLES parameter.

This in the parfiles fails:

SCHEMAS=MYSCHEMA
INCLUDE=TABLE:"IN ('TEST')"

This works:

TABLES=TEST

1/15/20

My bug number for what it’s worth: 30763851. Probably will be a duplicate since there are several already out there. I don’t think there is a patch yet.

6/3/20

I applied the supplied patch today and it works great. Boom!