Shulou(Shulou.com)05/31 Report--

What is the analysis and repair of Oracle partition data problems? I believe many inexperienced people are at a loss about this. Therefore, this paper summarizes the causes and solutions of the problems. Through this article, I hope you can solve this problem.

Today, based on the feedback from my colleagues, I dealt with the problem of a partitioned table, which also gave me a better understanding of Oracle's partitioned table function.

First of all, according to the feedback of the development colleagues, when they insert part of the data in bulk, some of the requests always fail, and viewing the log is the error of ORA-14400. For this kind of problem, I have a very intuitive feeling that there is something wrong with the partition.

> INSERT INTO DY_USER_ANALYSIS_MIN (ID,STAT_TIME,GAME_TYPE,ZONE_ID,GROUP_ID,ONLINE_5CNT)

VALUES (100 pz',to_number (- 1), to_number (- 1), to_number (0))

INSERT INTO DY_USER_ANALYSIS_MIN (ID,STAT_TIME,GAME_TYPE,ZONE_ID,GROUP_ID,ONLINE_5CNT)

*

ERROR at line 1:

ORA-14400: inserted partition key does not map to any partition

If you change 'pz'' to another string, 'dhsh'' will be fine.

So for such an ORA problem, I get a basic inference from the initial information, that is, there are no qualified partitions. If you analyze it carefully, you will find that there seems to be something strange about this problem.

General partition tables are Range partitions, basically numerical range or date to do range partitioning, how to understand this problem, if according to the time partition, then another SQL insert should also fail.

So in doubt, I looked at the partition and found that the table had a partition with the default key value maxvlue, so it doesn't make sense to say that the specified Range partition doesn't exist.

If this problem should be solved, an intuitive place is to look at the DDL,dbms_metadata.get_ddl of the table.

As soon as I saw the list, I was a little confused. How did the development students know about this DDL partition? they have already used this rather advanced feature, that is, the Range-list partition.

PARTITION BY RANGE ("STAT_TIME")

SUBPARTITION BY LIST ("GAME_TYPE")

SUBPARTITION TEMPLATE (

SUBPARTITION "SP_ABC" values ('abc')

TABLESPACE "TEST_DATA"

. . .

SUBPARTITION "SP_OTHER" values ('xjzj',' hij'

) TABLESPACE "TEST_DATA")

(PARTITION "P_OLD" VALUES LESS THAN (TO_DATE ('2015-01-01 00 VALUES LESS THAN,' SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

For this kind of problem, although it is still a little strange, but there are still some partitioned table foundation, so the analysis will not have too much deviation.

According to the DDL format, we want to modify the subpartition template rules of template.

Alter table TLSTAT_NEWBG.DY_USER_ANALYSIS_MIN

Set SUBPARTITION TEMPLATE (

SUBPARTITION "SP_ABC" values ('abc')

TABLESPACE "TEST_DATA"

. . .

SUBPARTITION "SP_OTHER" values ('xjzj',' hij','pz')

TABLESPACE "TEST_DATA")

There is no problem modifying the template in this way, and then continue to try to insert the data and find the same error. What's the problem at this time?

Based on the error troubleshooting, it still points to the definition of the partition, so let's take a look at one of the partitions.

(PARTITION "P_OLD" VALUES LESS THAN (TO_DATE ('2015-01-01 00 VALUES LESS THAN),' SYYYY-MM-DD HH24:MI:SS','NL

Gregory Gregorian)

TABLESPACE "TEST_DATA"

(SUBPARTITION "P_OLD_SP_ABC" VALUES ('abc')

TABLESPACE "TEST_DATA"

. . .

SUBPARTITION "P_OLD_SP_OTHER" VALUES ('xjzj', hij',' pz')

TABLESPACE "TEST_DATA")

Therefore, according to the definition of partition, the numerical range information of this subpartition is still missing.

If you want to regenerate a new subpartition, you can use the following ways:

ALTER TABLE TLSTAT_NEWBG.DY_USER_ANALYSIS_MIN MODIFY PARTITION P_OLD add SUBPARTITION P_OLD_SP_OTHER_pz VALUES ('pz')

If you want to generate the default subpartition name, you can use the following ways:

ALTER TABLE TLSTAT_NEWBG.DY_USER_ANALYSIS_MIN MODIFY PARTITION P2017_Q2 add SUBPARTITION VALUES ('pz')

At this time of the subpartition information, I excerpted one to have a simple look.

(SUBPARTITION "P2017_Q3_SP_ABC" VALUES ('abc')

TABLESPACE "TEST_DATA"

. . .

SUBPARTITION "P2017_Q3_SP_OTHER" VALUES ('xjzj',' hij') TABLESPACE "TEST_DATA"

SUBPARTITION "SYS_SUBP22" VALUES ('pz')

TABLESPACE "TEST_DATA")

If you are still not satisfied, let's use merge subpartitions. Of course, there will be a global lock in this operation, which will integrate the two partitions into one.

ALTER TABLE TLSTAT_NEWBG.DY_USER_ANALYSIS_MIN MERGE SUBPARTITIONS P2017_Q2_SP_OTHER,SYS_SUBP21 INTO SUBPARTITION P2017_Q2_SP_OTHER

After reading the above, have you mastered how to analyze and fix Oracle partition data problems? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.