从11.2.0.4倒入12c报错:ORA-39346

郑全

[size=130%]Histogram Data is Corrupted After Importing Data Using Impdp or ORA-39346 is Raised While Executing Impdp (文档 ID 2105130.1)

Applies to:   Oracle Database - Enterprise Edition - Version 11.1.0.7 to 11.2.0.4 [Release 11.1 to 11.2]
Information in this document applies to any platform.

SymptomsHistogram data is corrupted after importing data using impdp or ORA-39346 is raised while importing data (in Oracle 12c) under following conditions:

• Table has NVARCHAR2 column or NCHAR column, and multibyte data is inserted in.
• Above columns have histogram data.
• Expdp is done in Oracle 11.2.0.4 or prior release.
  

Version matrix of the reproducibility for this problem:

Source DB	Target DB	Histogram corrupted?	ORA-39346 raised?
11.1.0.7	11.1.0.7	Yes	No
11.1.0.7	11.2.0.4	Yes	No
11.1.0.7	12.1.0.2	Yes	Yes
11.2.0.4	11.2.0.4	Yes	No
11.2.0.4	12.1.0.2	Yes	Yes
12.1.0.1	12.1.0.1	No	No
12.1.0.2	12.1.0.2	No	No

NOTE:
The error ORA-39346 was introduced in Oracle 12c to check the data.
CauseThis problem was investigated in unpublished Bug 22095029 - ORA-39346 WHILE IMPORTING DATA WHICH IS INCLUDING NVARCHAR2 DATA and concluded that it is a restriction in 12c.
The documentation Bug 23722728 was created to correct the 12c documentation. In the "Exporting and Importing Between Different Database Releases" section of chapter 1, the following bullet item has been added:

"Importing Oracle Database 11g dump files that contain table statistics into Oracle Database 12c Release 1 (12.1) or later results in an Oracle ORA-39346 error.
This is because Oracle Database 11g dump files contain table statistics as metadata, whereas Oracle Database 12c Release 1 (12.1) and later expect table statistics to be presented as table data.
The workaround is to ignore the error and after the import operation completes, regather table statistics."


SolutionAs a workaround, re-gather histogram data after executing impdp, e.g.
SQL> exec dbms_stats.gather_table_stats ('TEST', 'TEST_TAB', method_opt => 'FOR ALL COLUMNS');
To avoid the corruption or ORA-39346 error, you can use exclude=table_statistics option while executing impdp, then re-gather histogram data.
> impdp test/test tables=test_tab directory=exp_dir exclude=table_statistics

SQL> exec dbms_stats.gather_table_stats ('TEST', 'TEST_TAB', method_opt => 'FOR ALL COLUMNS');