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