Fast Split Partition

Try to split  yearly partition into monthly 12 partitions , it took more than 7 hours , and finally failed for splitting January one.

SQl>alter table CDR split partition PAR_2018 at ( 
    TO_DATE(' 2018-02-01 00:00:00','sYYYY-MM-DD HH24:MI:SS') ) 
    into ( partition par_201801, partition PAR_2018 ) update indexes;
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS4'

Elapsed: 07:19:47.81

For 11gR2, here is the faster way of splitting the partition.

1) Disable the indexes.

SQL>alter index CDR_IDX1 unusable;
SQL>alter index CDR_IDX2 unusable;
SQL>alter index CDR_IDX3 unusable;
SQL>alter index CDR_IDX4 unusable;

2) Split partition with parallel option.

SQl>alter table CDR split partition PAR_2018 at ( 
    TO_DATE(' 2018-02-01 00:00:00','sYYYY-MM-DD HH24:MI:SS') ) 
    into ( partition par_201801, partition PAR_2018 )
    parallel 8;

Table altered.

Elapsed: 00:00:43.75

3) Rebuild indexes with parallel option.

SQL> alter index CDR_IDX1 rebuild parallel 16;

Index altered.

Elapsed: 00:01:26.70

SQL> alter index CDR_IDX2 rebuild parallel 16;

Index altered.

Elapsed: 00:00:42.96

SQL> alter index CDR_IDX3 rebuild parallel 16;

Index altered.

Elapsed: 00:00:41.34

SQL> alter index CDR_IDX4 rebuild parallel 16;

Index altered.

Elapsed: 00:00:47.16



SQL>  alter index CDR_IDX1 parallel 1;

Index altered.

Elapsed: 00:00:00.01
SQL>  alter index CDR_IDX2 parallel 1;

Index altered.

Elapsed: 00:00:00.03
SQL>  alter index CDR_IDX3 parallel 1;

Index altered.

Elapsed: 00:00:00.01
SQL>  alter index CDR_IDX4  parallel 1;

Index altered.

Elapsed: 00:00:00.01

For 12c onward, split a partition into multiple partitions in one statement.

SQL>alter table CDR split partition PAR_2018 into ( 
partition par_201801 values less than TO_DATE(' 2018-02-01 00:00:00','sYYYY-MM-DD HH24:MI:SS')),
partition par_201802 values less than TO_DATE(' 2019-03-01 00:00:00','sYYYY-MM-DD HH24:MI:SS')),
partition par_201903 values less than TO_DATE(' 2019-04-01 00:00:00','sYYYY-MM-DD HH24:MI:SS')),
...
..
.
partition par_201811 values less than TO_DATE(' 2018-12-01 00:00:00','sYYYY-MM-DD HH24:MI:SS')),
partition par_201812 values less than TO_DATE(' 2019-01-01 00:00:00','sYYYY-MM-DD HH24:MI:SS'))
);