请求协助解决ORA-14400错误：非分区表转分区表INSERT报错

Hey there, let's break down how to resolve that ORA-14400 error you're facing during your INSERT INTO SELECT operation while migrating to a partitioned database. First, a quick reminder: ORA-14400: inserted partition key does not map to any partition means the rows you're trying to insert don't match any existing partition or subpartition defined on TABLE_1.

Since TABLE_1 uses LIST-RANGE composite partitioning (LIST as the top-level partition, RANGE as subpartitions), let's walk through the most common causes and fixes:

1. Verify Source Data Against Partition Definitions #

First, you need to check if your source data contains values that fall outside the bounds of your defined partitions/subpartitions:

a. Check LIST Partition Key Values #

Your top-level LIST partition FIRST includes values 'D' and 'T'. Any rows in the source table with a LIST key column value outside this set will fail to find a matching partition. Run this query to identify such rows:

SELECT DISTINCT your_list_key_column
FROM source_table
WHERE your_list_key_column NOT IN ('D', 'T');

b. Check RANGE Subpartition Date Values #

For rows where the LIST key is 'D' or 'T', you need to ensure their date values fit within the defined subpartition ranges. You mentioned subpartitions up to FIRST_3, but if your source data has dates earlier than 2017-07-01 or later than the high value of FIRST_3, those rows won't have a matching subpartition. Use this query to spot them:

SELECT DISTINCT your_range_date_column
FROM source_table
WHERE your_list_key_column IN ('D', 'T')
  AND (your_range_date_column < TO_DATE('2017-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
       OR your_range_date_column >= (SELECT MAX(high_value) FROM user_tab_subpartitions WHERE table_name = 'TABLE_1'));

2. Add Missing Partitions/Subpartitions #

Once you've identified the out-of-bounds values, you'll need to create the necessary partitions to accommodate them:

a. Add a New LIST Partition #

If you found other LIST key values (e.g., 'X'), create a new top-level partition:

ALTER TABLE TABLE_1 ADD PARTITION SECOND VALUES ('X');

Alternatively, if you want to catch all unlisted values, create a DEFAULT LIST partition (only if your business logic allows this):

ALTER TABLE TABLE_1 ADD PARTITION DEFAULT_PART VALUES (DEFAULT);

b. Add New RANGE Subpartitions #

For dates that fall outside existing subpartitions under the FIRST partition, add a new subpartition:

ALTER TABLE TABLE_1 MODIFY PARTITION FIRST
ADD SUBPARTITION FIRST_4 VALUES LESS THAN (TO_DATE('2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));

Adjust the date value to match the next logical range for your data.

3. Validate Partition Key Data Types #

Make sure the data types of the partition key columns in your source table exactly match those in TABLE_1. For example:

• If your target table's date column is DATE type, ensure the source table's date column isn't stored as a VARCHAR2 (which could lead to implicit conversion errors that make dates appear out of range).
• Check character set and length for the LIST key column to avoid mismatches (e.g., a VARCHAR2(1) column in the target vs VARCHAR2(2) in the source could cause unexpected value truncation).

4. Consider Alternative Loading Methods (For Large Datasets) #

If you're dealing with a huge volume of data, a direct INSERT INTO SELECT might be inefficient or harder to troubleshoot. Instead:

1. Filter the source data into temporary tables grouped by partition key values.
2. Use partition exchange loading to swap these temporary tables into the corresponding partitions/subpartitions of TABLE_1. This is faster and reduces the risk of bulk insert errors.

内容的提问来源于stack exchange，提问作者erewien