Oracle Data pump Import partition table statistic information error (c)

Recently checked a blog before, unexpectedly found this bug was I forget, after a year to continue to solve this problem.

Based on the analysis process described in the previous article, it is generally possible to confirm that the statistics of these partitioned tables are related to themselves.

Since the current database is exported from 920 environment exp, IMP is imported into the same version of the intermediate database and eventually imported into the current database through the data pump IMPDP. The partitioned table was created manually in the intermediate database and the Ignore=y parameter was specified when IMP was imported, because it was unable to resolve the conversion problem of the table space.

And now the problem is that on all of the user's partition tables, the problem is related to the migration process. Checked the script at the time and found no anomalies.

However, since the original version of the data is 9204 and the migrated version is 10203, it is possible that the different versions have caused some changes in the settings of some parameters during the migration.

The statistics related to the table's storage parameters are monitoring. This storage parameter is interesting, introduced from 9i, and in the 10g and later versions, this parameter disappears again. Not that Oracle thought this parameter was meaningless, but that Oracle thought the monitoring feature was very costly and useful for statistics, so it became the default behavior of Oracle, Only one implicit parameter is left to control whether the monitoring operation is performed.

Check the monitoring properties of these tables and discover that the values are yes:

sql> SELECT table_name, monitoring

2 from User_tables

3 WHERE table_name in

4 (SELECT table_name from user_part_tables);

TABLE_NAME MON

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

Emed_web_log YES

Ord_log_hit_comm YES

Ord_purchase_item YES

Ord_order_item YES

Ord_order YES

Con_log_list_item YES

6 rows have been selected.

But the source of monitoring values in the 10g and 9i data dictionaries is different, in 9i:

Sql> SELECT TEXT from dba_views WHERE view_name = ' dba_tables ';

TEXT

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

Select U.name, O.name, Decode (Bitand (T.property, 4194400), 0, ts.name, null),

Decode (Bitand (T.property, 1024), 0, NULL, co.name),

Decode ((T.property, Bitand) +bitand (T.flags, 536870912)),

0, NULL, co.name),

Decode (Bitand (T.property, 32+64), 0, mod (t.pctfree$, MB), 0, NULL),

Decode (Ts.flags, Bitand), To_number (NULL),

Decode (Bitand (T.property, 32+64), 0, t.pctused$, 0, null)),

Decode (Bitand (T.property), 0, T.initrans, null),

Decode (Bitand (T.property), 0, T.maxtrans, null),

S.iniexts * Ts.blocksize,

Decode (Bitand (Ts.flags, 3), 1, To_number (NULL),

S.extsize * ts.blocksize),

S.minexts, S.maxexts,

Decode (Bitand (Ts.flags, 3), 1, To_number (NULL),

s.extpct),

Decode (Ts.flags, Bitand), To_number (NULL),

Decode (Bitand (O.flags, 2), 2, 1, decode (s.lists, 0, 1, s.lists)),

Decode (Ts.flags, Bitand), To_number (NULL),

Decode (Bitand (O.flags, 2), 2, 1, decode (s.groups, 0, 1, s.groups)),

Decode (T.property, bitand), NULL,

Decode (Bitand (T.flags), 0, ' YES ', ' NO '),

Decode (Bitand (t.flags,1), 0, ' Y ', 1, ' N ', '? '),

T.ROWCNT,

Decode (Bitand (T.property,), 0, t.blkcnt, null),

Decode (Bitand (T.property,), 0, t.empcnt, null),

T.AVGSPC, t.chncnt, T.avgrln, T.AVGSPC_FLB,

Decode (Bitand (T.property,), 0, t.flbcnt, null),

Lpad (Decode (T.degree, 32767, ' DEFAULT ', NVL (t.degree,1)), 10),

Lpad (Decode (t.instances, 32767, ' DEFAULT ', NVL (t.instances,1)), 10),

Lpad (Decode (Bitand (T.flags, 8), 8, ' Y ', ' N '), 5),

Decode (Bitand (T.flags, 6), 0, ' ENABLED ', ' DISABLED '),

T.samplesize, T.analyzetime,

Decode (Bitand (T.property,), ' YES ', ' NO '),

Decode (T.property, Bitand), the ' IOT ',

Decode (T.property, Bitand), the ' Iot_overflow ',

Decode (Bitand (T.flags, 536870912), 536870912, ' iot_mapping ', null

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