Thursday, August 31, 2017

A workaround for ORA-12008 and ORA-14400 during online redefinition

To introduce partitions in one of my tables, I was using online redefinition of a rather large table.
The following error was thrown after about 30 minutes after having started the redefinition with dbms_redefintion.start_redef:
begin
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-12801: error signaled in parallel query server P004
ORA-14400: inserted partition key does not map to any partition
ORA-06512: at "SYS.DBMS_REDEFINITION", line 75
ORA-06512: at "SYS.DBMS_REDEFINITION", line 3459
ORA-06512: at line 2

It turned out that there were NULL values in the column that would be the future partition key.
Everything was explained very well in Doc ID 2103273.1 "ORA-14400: inserted partition key does not map to any partition, with possible ORA-12008: error in materialized view refresh path".

You can solve this in two ways:

a) find the rows with null values, and update them

or

b) use an overflow partition which will work as a "catch all" basket for rows that can't be mapped to a specific partition. NULL values sort in this category.

Since I was using interval range partitioning, I had to choose the former of the two options. If this is not possible, you can't use interval partitioning, and need to explicitly define every partition + your overflow partition in your interim table.

No comments:

Post a Comment