Split Partition

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:

1. 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: Both column will tell the type of partition present in table
Partition Type – RANGE , HASH , LIST
Subpartition Type – RANGE , HASH , LIST

Note: RANGE and LIST partition can be merged or split.
 
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
Break the Monthly partition into 15 days partitions
SYNTAX:

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
);

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';