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