Oracle Data Pump Import partition Table statistics error (ii) in-depth study of the phenomenon of the problem

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

The previous article recorded the phenomenon of the problem, and this one continues to be studied in depth.

The previous article has described the problem, and it is difficult to reproduce the problem. In any case, to simulate the actual situation, can not reproduce the problem.

To reproduce this problem, in a RAC database environment, the problem can not be reproduced by creating a partitioned table modeled on the problem table and collecting statistical information in the same way as the problem database.

However, using the statistics exported by the problem database, you can reproduce the problem. Problems found in the Friday, but because the data are inconvenient to bring home, so due to time constraints only so much testing.

We went to the company early this morning and went on to test the problem. On the weekends, if you can't reproduce the problem on the test database, but you can reproduce the problem with the data from the source database, then the problem is related to the data itself, which is to say, the problem with the data itself is causing the bug.

So today when the test to try to use the problem data, in many different platforms Oracle10203 RAC and Single-instance database to try to perform data pump import, the error has been tried. In this case, the problem is caused by the source database itself, but also with the current data, statistics information of the source database.

This moves the target of the problem from the test database to the source database where the export is performed.

First, comparing the source database with the table analysis in the import database, you can find that only the statistics for the partitioned table are not imported, which further determines that the problem occurs in the partitioned table.

The following query analysis of the table in the source database:

Sql> SELECT table_name, partitioned, temporary, last_analyzed

2 from User_tables

3 ORDER by 4 DESC;

TABLE_NAME PAR T last_analyzed

------------------------------ --- - -------------------

Ord_hit_comm_tmp NO Y

Con_list_item_share_tmp NO Y

Job_month_stats NO Y

Zzz_purchase_buyer NO N 2008-03-02 02:14:45

Zzz_yangs_pro NO N 2008-03-02 02:14:45

Zzz_yangs_order3 NO N 2008-03-02 02:14:45

Zzz_yangs_order2 NO N 2008-03-02 02:14:45

.

.

.

Ass_bbs_catalog NO N 2008-03-02 01:00:10

Ass_bbs_article No n 2008-03-02 01:00:09a no n 2008-03-02 01:00:08

Ord_log_hit_comm YES N 2007-05-03 15:33:45

Con_log_list_item YES N 2007-05-03 15:33:19

Ord_purchase_item YES N 2007-05-03 15:33:17

Ord_order_item YES N 2007-05-03 15:30:25

Ord_order YES N 2007-05-03 15:23:42

450 rows have been selected.

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

From here you can see that the data analysis of the partition table is problematic, the other tables are analyzed in the near future, only the analysis of the partition table has not been updated for a long time.

To examine the profiling method for a partitioned table:

Sql> SELECT WHAT, last_date from user_jobs WHERE JOB = 291;

WHAT last_date

-------------------------------------------------------- -------------------

Dbms_stats.gather_schema_stats (user, Cascade => true); 2008-03-02 01:00:02

In this way, you should analyze the partition tables by default, and then check that each partition of the partitioned table is analyzed:

Sql> SELECT table_name, partition_name, last_analyzed

2 from User_tab_partitions

3 ORDER by 1, 2;

TABLE_NAME Partition_name last_analyzed

------------------------------ ------------------------------ --------------

Con_log_list_item CON0610

Con_log_list_item CON0611

Con_log_list_item CON0612

Con_log_list_item CON0701

Con_log_list_item Con_max

Ord_log_hit_comm HLG0610

Ord_log_hit_comm HLG0611

.

.

.

Ord_purchase_item ORD0804

Ord_purchase_item ORD0807

Ord_purchase_item ORD0810

Ord_purchase_item ORD0901

97 rows have been selected.

None of the partitions have been analyzed. Check that partitioned tables under other schemas in the database also have the same problem:

Sql> SELECT A.owner, A.table_name, last_analyzed

2 from All_tables A, All_part_tables B

3 WHERE a.table_name = B.table_name

4 and A.owner = B.owner

5 and A.owner in (' Zhejiang ', ' Anhui ', ' BEIJING ')

6 ORDER by 3;

OWNER table_name last_analyzed

---------- ------------------------------ -------------------