Fast Split Partition and Split Subpartition

Fast split partition is very useful in production environment where outage is unavailable, and no impact on partitioned tables. Please refer to the details in Fast SPLIT PARTITION and SPLIT SUBPARTITION

Here is a good example of using fast split partition operation. For some reason, table CDR is partitioned by transaction date, and the invalid CDR records created for future date with future partitions created.

SQL> select sysdate from dual;

SYSDATE
---------
24-JUL-19

TABLE_NAME    PARTITION  INTERVAL
------------- ---------- --------- ---------------------------
CDR           RANGE      NUMTODSINTERVAL(1,'DAY')

TABLE_NAME PARTITION_NAME   HIGH_VALUE                    NUM_ROWS
---------- -------------- ------------------------------ ---------
CDR        P_FIRST        TIMESTAMP' 1900-01-01 00:00:00'         0
CDR        SYS_P707668    TIMESTAMP' 2019-09-04 00:00:00' 296956241
CDR        SYS_P708049    TIMESTAMP' 2019-09-05 00:00:00'         0
CDR        SYS_P708687    TIMESTAMP' 2019-09-06 00:00:00'         0
CDR        SYS_P709207    TIMESTAMP' 2019-09-07 00:00:00'         0

After cleaned the invalid CDR records, the future partitions should be split and dropped.

SQL> alter table CDR set interval ();

Table altered.


SQL> alter table CDR drop partition SYS_P709207 update indexes;

Table altered.


SQL> alter table CDR drop partition SYS_P708687 update indexes;

Table altered.

SQL> alter table CDR drop partition SYS_P708049  update indexes;

Table altered.

SQL> alter table CDR split partition SYS_P707668 at 
     ( TO_DATE(' 2019-07-25 00:00:00','sYYYY-MM-DD HH24:MI:SS') ) 
into ( partition par_20190724, partition SYS_P707668 ) update indexes;

Table altered.


SQL> select count(*) from CDR partition ( SYS_P707668);

COUNT(*)
----------
0

SQL> alter table CDR drop partition SYS_P707668 update indexes;

Table altered.

SQL> alter table CDR set interval (NUMTODSINTERVAL(1,'DAY'));

Table altered.


TABLE_NAME  PARTITION_NAME  HIGH_VALUE                      NUM_ROWS
----------- --------------- -------------------- ---------- ---------
CDR         P_FIRST         TIMESTAMP' 1900-01-01 00:00:00'        0
CDR         PAR_20190724    TIMESTAMP' 2019-07-25 00:00:00' 297116561
CDR         SYS_P709208     TIMESTAMP' 2019-07-26 00:00:00'    123456
CDR         SYS_P709923     TIMESTAMP' 2019-07-27 00:00:00'    234567
...
..
.

Partition split is very fast, because all the records go to one partition, and the other partition is empty.

ORA-14758: Last partition in the range section cannot be dropped

Try to drop the last interval partition and get error “ORA-14758: Last partition in the range section cannot be dropped”.

SQL>alter table PART_TAB drop partition SYS_P3726162 update indexes;
alter table PART_TAB drop partition SYS_P3726162 update indexes
*
ERROR at line 1:
ORA-14758: Last partition in the range section cannot be dropped

WORKAROUND

Disable interval partition definition, then drop the last partition and re-enable the interval partition again.

SQL> alter table PART_TAB set interval();

Table altered.


SQL> alter table PART_TAB drop partition SYS_P3726162 update indexes;

Table altered.


SQL>alter table PART_TAB set INTERVAL (NUMTODSINTERVAL(1,'DAY'));

Table altered.