Oracle Data Pump Import partition Table statistics error (i) the phenomenon of the problem

Today, a bug was found in the data pump import operation.

Database version Oracle 10203 for Solaris RAC, performing import statistics in the processing table The times wrong, the error message is: ORA-39083 and ORA-917.

After careful investigation, comparing the source database analysis and the target database table analysis, found that all the partition table statistics are not imported.

The basic confirmation problem is that IMPDP has a problem processing the statistics for the partitioned table.

Unfortunately, the problem cannot be reproduced by constructing a case: in the target database (the RAC environment) trying to set up partitioned tables and performing the export and import of the data pump, the problem cannot be reproduced. After importing the problematic partition table in the source database and collecting statistics in the same way as the source database, the problem still cannot be reproduced.

However, you can reproduce this problem simply by exporting the statistics from the source database and importing them into the target database:

bash-2.03$ IMPDP test/test directory=d_test DUMPFILE=ZHEJIANG_ORDER.DP logfile=zhejiang_order.log Zhejiang:test Include=table/statistics

Import:release 10.2.0.3.0-64bit Production on Monday, 31 December, 2007 2:49:30

Copyright (c) 2003, +, Oracle. All rights reserved.

Connect to: Oracle Database 10g Enterprise Edition release 10.2.0.3.0-64bit Production

This article URL address: http://www.bianceng.cn/database/Oracle/201410/45379.htm

With the partitioning, real application clusters, OLAP and Data Mining options have successfully loaded/unloaded the primary table "TEST". Sys_import_full_01 "Start" TEST "." Sys_import_full_01 ": test/******** directory=d_test dumpfile=zhejiang_order.dp logfile=zhejiang_order.log remap_ Schema=zhejiang:test Include=table/statistics handles object types Table_export/table/table_data processing object Types table_export/table/ Statistics/table_statistics

ORA-39083: Object type Table_statistics creation failed, error occurred:

ORA-06550: line 12th, column 17th:

pl/sql:ora-00917: Missing comma

ORA-06550: Line 4th, column 115th:

Pl/sql:sql Statement ignored

ORA-06550: Line 20th, column 17th:

pl/sql:ora-00917: Missing comma

ORA-06550: Line 12th, column 161th:

Pl/sql:sql Statement ignored

ORA-06550: Line 28th, column 17th:

pl/sql:ora-00917: Missing comma

ORA-06550: Line 20th, column 161th:

Pl/sql:sql Statement ignored

ORA-06550: line 36th, column 17th:

pl/sql:ora-00917: Missing comma

ORA-06550: Line 28th, 1th job "TEST". Sys_import_full_01 "has been completed, but there are 1 errors (completed at 02:49:38)

Query based on key information in Metalink, only to find an article in the test of other bugs encountered this problem, but Oracle's developers do not deal with this problem.

The real reason for the problem can not be found within a short time, so we should first record the cause and environment of the problem. That's a big question, isn't it? As long as the import after the collection of information on the partition table, but said that the small is not small, if you ignore this error, the lack of statistical information is the partition table, generally speaking, the partition table is a large table in the system, access is also more frequent, more importantly, Because the presence of a partitioned table Oracle will certainly use the CBO as an optimization model, the use of default statistics, or the use of dynamically collected information, is likely to result in inefficient execution plans that can paralyze the entire system.

Author: 51cto Blog Oracle Little Bastard