Split Partition in Oracle

Split Partition in Oracle means to Split the one partition into two or more Partitions.
E.g. Quarterly partition split into monthly partition.

Following are the steps to do splitting of Table Partition in Oracle:

Check the partition table present in schema.

set line 200 pages 200
col table_name for a10
col partitioning_type for a10
col subpartitioning_type for a10
select table_name, partitioning_type, subpartitioning_type from dba_part_tables where owner='SCOTT';

Note:
RANGE and LIST partition can be merged or split.
Both column will tell the type of partition present in table
Partition Type – RANGE , HASH , LIST
Subpartition Type – RANGE , HASH , LIST

SPLIT RANGE PARTITION:
Example: Having Range Partition on Table “TRAN” on date column.

1. Check the partition name and high value in Table.

select table_name, partition_name, high_Value from DBA_TAB_PARTITIONS where table_owner = 'SCOTT' and table_name ='TRAN';

Table_name Partition_name High_value
---------- -------------- ------------
TRAN	   IC201701	  TO_DATE(' 2017-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')
TRAN	   IC201702	  TO_DATE(' 2017-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')

2: Split the partition.
Following example break the Monthly partition into 15 days partitions.

ALTER TABLE tran split PARTITION IC201701
AT ( TO_DATE(' 2017-01-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS') )
into (
PARTITION IC201701_1 tablespace IC201701,
PARTITION IC201701_2 tablespace IC201701
);

---Use the "UPDATE INDEXES" clause for updating index with command:

ALTER TABLE tran split PARTITION IC201701
AT ( TO_DATE(' 2017-01-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS') )
into (
PARTITION IC201701_1 tablespace IC201701,
PARTITION IC201701_2 tablespace IC201701
) update indexes;

Example: Suppose if you want to split the partition in weekly wise from monthly, Then you need to do it one by one:

ALTER TABLE ic.tran split PARTITION IC201701
AT ( TO_DATE(' 2017-01-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS') )
into (
PARTITION IC201701_1 tablespace IC,
PARTITION IC201701 tablespace IC
);

ALTER TABLE ic.tran split PARTITION IC201701
AT ( TO_DATE(' 2017-01-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS') )
into (
PARTITION IC201701_2 tablespace IC,
PARTITION IC201701 tablespace IC
);

ALTER TABLE ic.tran split PARTITION IC201701
AT ( TO_DATE(' 2017-01-22 00:00:00', 'SYYYY-MM-DD HH24:MI:SS') )
into (
PARTITION IC201701_3 tablespace IC,
PARTITION IC201701_4 tablespace IC
);

Example for online split.

----Split partition online (12cR2 only)
SQL> alter table tran split partition IC201601 into
(partition IC201601_P1 VALUES LESS THAN (TO_DATE('15/01/2016', 'DD/MM/YYYY')),PARTITION IC201601_p2) ONLINE;
Table altered.
--Check partition
SQL> select partition_name,read_only,high_value from dba_tab_partitions where table_name='TRAN';

3. Check the upper query for verification.

select Table_name,Partition_name,High_Value from DBA_TAB_PARTITIONS where table_owner = 'SCOTT' and table_name ='TRAN';

Table_name Partition_name High_value
---------- -------------- ------------
TRAN	   IC201701_1	  TO_DATE(' 2017-01-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')
TRAN	   IC201701_2	  TO_DATE(' 2017-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')

4. Check the index is stable state.

SELECT 'ALTER INDEX ' || OWNER || '.' ||
INDEX_NAME || ' REBUILD ' ||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_INDEXES
WHERE STATUS='UNUSABLE'
UNION
SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' ||
INDEX_NAME ||
' REBUILD PARTITION ' || PARTITION_NAME ||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_IND_PARTITIONS
WHERE STATUS='UNUSABLE'
UNION
SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' ||
INDEX_NAME ||
' REBUILD SUBPARTITION '||SUBPARTITION_NAME||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_IND_SUBPARTITIONS
WHERE STATUS='UNUSABLE';