impdp ORA-06550 ORA-00917

Just want to quickly write about this bug.

I created a Datapump export (expdp) of a partitioned table from an Oracle 10.2.0.3 database, and import (impdp) into an Oracle 10.2.0.4 database.

During impdp, everything worked well until importing statistics, then the process ran into the following errors:

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39083: Object type TABLE_STATISTICS failed to create with error:
ORA-06550: line 12, column 17:
PL/SQL: ORA-00917: missing comma
ORA-06550: line 4, column 121:
PL/SQL: SQL Statement ignored
…

Turns out this is a bug with expdp/impdp partitioned table, described in Metalink Note “Bug 6862987 – impdp throws an error when importing partitioned table stats”, which happens “when importing statistics for tables if there are global statistics only.”. This bug is fixed in the 10.2.0.5 Patchset. The workaround is not to import statistics.

I dumped the sqlfile and had a look what’s really going on, it turns out there was a repeated syntax error with some of the INSERT statements:

INSERT INTO “SYS”.”IMPDP_STATS” (type, version,
c1, c2, c3, c4, c5, n1,
n2, n3, n4, n5, n6, n7, n8, n9, n10, n11,
d1, r1, r2, ch1, flags)
VALUES (‘C’, 4, ‘HTTP_LOG’, ‘P20090921’, NULL, ‘PROTOCOL_ID’, ‘PBC_ADMIN’,
0, 0, 0, NULL, 0, NULL, NULL, 0, NULL, NULL, NULL,
‘, NULL), NULL, NULL, NULL, 0);