创建计划分区遭遇ORA-14074:错误

  创建计划分区的时候报错。错误信息如下：

plan_ids := 1
table := MSC_ATP_SUMMARY_SD
partition_name : ATP_SUMMARY_SD_1
In exception of CREATE_PARTITIONS
sqlerrm := ORA-14074: partition bound must collate higher than that of the last
partition

从msc_plan_partitions表可以查到这个计划分区,但从all_tab_partitions表中却查不到。查了一下metalink找到一篇文章解决了这个问题。原文如下：

 

Applies to: Oracle Advanced Supply Chain Planning - Version: 11.5.5 to 11.5.10.2
This problem can occur on any platform.
MSCCFPAR - Create Plan Partition

Symptoms

DO NOT USE THIS FOR R 12 ISSUES - see Known Issues for R12

ISSUE CLARIFICATION
====================

Customer encountered an error running MSCCRPAR module: Create APS Partitions

+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
j := 1
table := MSC_ATP_SUMMARY_SO
partition_name : ATP_SUMMARY_SO__1
l_count := 1
Partition for instance 1 already exists
table := MSC_ATP_SUMMARY_SD
partition_name : ATP_SUMMARY_SD__1
l_count := 1
Partition for instance 1 already exists
l_share_partition := N
plan count := 5
j := 1
plan_ids := 1
table := MSC_ATP_SUMMARY_SD
partition_name : ATP_SUMMARY_SD_1
In exception of CREATE_PARTITIONS
sqlerrm := ORA-14074: partition bound must collate higher than that of the last
partition
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+

Checked the table setups for partitioned tables using the following SQL

 

SELECT
  table_name,
  partition_name,
  num_rows,
  high_value,
  sample_size,
  last_analyzed,
  global_stats
FROM
  all_tab_partitions
WHERE
  table_name like 'MSC%'  
  order by  substr(partition_name,instr(partition_name,'_',-1,1)+1)

 

Checked MSC_PLAN_PARTITIONS and MSC_INST_PARTITIONS tables for Plan and Instance partitions.

 

-- Checking Plan Partitions
select      plan_id,
            plan_name,
            free_flag,
            partition_number
from        msc_plan_partitions

-- Checking Instance Partitions
select      instance_id,
            free_flag,
            creation_date,
            last_update_date           
from        msc_inst_partitions;

 

Found that the error reported in the log file matches with customer table which had 5 lines
They have 4 active plans and only 4 plan partitions when ALL_TAB_PARTITIONS info was reviewed.

BUT
they had a line with plan_id =1, partition_number = 1 in the table.
This did not exist in the tables created in all_tab_partitions.

Cause

1. The ATP portion of the process checks the table MSC_PLAN_PARTITIONS to see if a PLAN_ID/PARTITION_NUMBER exists which does not have the partitions created for the tables
MSC_ALLOC_SUPPLIES
MSC_ALLOC_DEMANDS
MSC_ATP_SUMMARY_SD
MSC_ATP_SUMMARY_RES
MSC_ATP_SUMMARY_SUP

2. If any table does not have a proper table existing for a PLAN_ID / PARTITION_NUMBER, then the program will correct this issue and create the table.

3. In this case, the PLAN_ID / PARTITION_NUMBER existed in the table MSC_PLAN_PARTITIONS and did not have any of the tables created in the system. It is likely that this plan partition was dropped manually instead of being dropped using the concurrent request Drop Partition.

NOTES:
1. Table MSC_ATP_SUMMARY_SO will only have partitions for _0 ( the template partition)
and for any ODS partition -- e.g. __1 - double underscore partition created

2. Table MSC_ATP_SUMMARY_SD will have __nn (double underscore - ODS Instance partition) plus _nn (single underscore - PDS Plan partition) created.

3. All other ATP tables will only have _nn (PDS Plan partition) partitions created.

Solution

1. Run the Drop Partition Request ** using the patition_number that does not have any partitions existing
OR
has incomplete partitions that have not been created correctly.

OR
IF the SQL does not reveal any corrupted partitions
THEN drop any Plan Partition has the plan name *UNUSABLE*  OR any plan partition with free_flag = 1

2. Then run the Create APS partitions request again and it should run just fine.

3. If # 1 fails, then it may be necessary to contact Oracle Support and reference this note number and upload the SQL results from the note so that further diagnosis can be conducted. 

4. If examination of the SQL outputs above does not reveal any mismatch in the partitions existing on the system, then a Trace with Binds (Level 4 trace) of the Create Partitions request will be required to investigate exactly where the error occurred.
-- See - # 12.2 - Using Trace with Binds for a Concurrent Request to provide this information.

** In 11.5.9 the request may be used to drop an instance partition as well as a plan partition.
Therefore there is an additional prompt in the request parameters.
Answer YES to the prompt Plan if the plan has the incorrect partitions created
OR answer NO if the problem exists with an instance partition.