Wednesday, February 12, 2014

How to work around ORA-14080: partition cannot be split along the specified high bound

Problem:
My table had partitions for Jan 2014 and March 2014. Any attempt to split the overflow partition at this point will throw the error message

ORA-14080: partition cannot be split along the specified high bound

The following query shows my partitions for year 2014:
SELECT  PARTITION_NAME, 
        HIGH_VALUE,
        NUM_ROWS
FROM    DBA_TAB_PARTITIONS
WHERE   TABLE_NAME ='SALES'
AND     TABLE_OWNER = 'SCOTT'
AND     (PARTITION_NAME LIKE '%Y2014' OR PARTITION_NAME LIKE '%MAX%')
ORDER BY PARTITION_NAME;

Output:
PARTITION_NAME HIGH_VALUE NUM_ROWS
SALES_M01_Y2014 TO_DATE(' 2014-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') 3936
SALES_M03_Y2014 TO_DATE(' 2014-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') 2308
SALES_MAX MAXVALUE 0

My solution:
Split the partition with the highest bound (March 2014) into two new ones, one that will take care of the rows for February 2014, and another one which will be empty:
SQL> 
  1  ALTER TABLE SCOTT.SALES
  2  SPLIT PARTITION SALES_M03_Y2014 AT (TO_DATE('2014-03-01', 'YYYY-MM-DD')) INTO (
  3  PARTITION SALES_M02_Y2014 TABLESPACE SCOTT_DATA,
  4* PARTITION SALES_MAX_NEW TABLESPACE SCOTT_DATA);

Table altered.
Rebuild local indexes, if any:
SQL> ALTER TABLE SCOTT.SALES MODIFY PARTITION SALES_M02_Y2014 REBUILD UNUSABLE LOCAL INDEXES;

Table altered.

Analyze the partitio, and check to make sure the that it really does contains zero rows. You can use the initial query in this post for that:
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','SALES','SALES_M02_Y2014');

PL/SQL procedure successfully completed.

Let's execute the query above once more:
PARTITION_NAME HIGH_VALUE NUM_ROWS
SALES_M01_Y2014 TO_DATE(' 2014-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') 3936
SALES_M02_Y2014 TO_DATE(' 2014-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') 2308
SALES_MAX MAXVALUE 0
SALES_MAX_NEW TO_DATE(' 2014-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') 0


Drop the temporary partition:
SQL> ALTER TABLE SCOTT.SALES DROP PARTITION SALES_MAX_NEW;

Table altered.
We now have:
PARTITION_NAME HIGH_VALUE NUM_ROWS
SALES_M01_Y2014 TO_DATE(' 2014-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') 3936
SALES_M02_Y2014 TO_DATE(' 2014-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') 2308
SALES_MAX MAXVALUE 0

No comments:

Post a Comment