In Oracle 12.2, We can split partitions or subpartitions online without impacting the DML statements.

SQL> select partition_name,read_only,high_value from dba_tab_partitions where table_name='ORDER_TAB';

PARTITION_NAME READ HIGH_VALUE
-------------------------------- ---- --------------------------------------------------------------------------------
CREATED_2105_P10 NO TO_DATE(' 2015-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
CREATED_2105_P11 NO TO_DATE(' 2015-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
CREATED_2105_P12 NO TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
CREATED_2105_P8 NO TO_DATE(' 2015-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
CREATED_2105_P9 NO TO_DATE(' 2015-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
CREATED_MX NO MAXVALUE

6 rows selected.

SQL> alter table order_tab split partition CREATED_MX into
(partition CREATED_2106_P2 VALUES LESS THAN (TO_DATE('01/03/2016', 'DD/MM/YYYY')),PARTITION CREATED_MX) ONLINE; 2

Table altered.

SQL> select partition_name,read_only,high_value from dba_tab_partitions where table_name='ORDER_TAB';

PARTITION_NAME READ HIGH_VALUE
-------------------------------- ---- --------------------------------------------------------------------------------
CREATED_2105_P10 NO TO_DATE(' 2015-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
CREATED_2105_P11 NO TO_DATE(' 2015-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
CREATED_2105_P12 NO TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
CREATED_2105_P8 NO TO_DATE(' 2015-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
CREATED_2105_P9 NO TO_DATE(' 2015-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
CREATED_2106_P2 NO TO_DATE(' 2016-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
CREATED_MX NO MAXVALUE

8 rows selected.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.