Step to Split table Partition in Oracle

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

 
 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s