Oracle Partitioning - Split Partition

At times when we initially create partitions we make them with a certain data load in mind. But after sometime/years we realize that the partition scheme is becoming a problem and there are certain partitions with more data and this can create performance problems. Here is how we can split the partitions to create smaller partitions or to say create partitions between already existing partitions.
Lets create a partition table first:

SQL> CREATE TABLE DATA_LOAD
2 ( DATA_id NUMBER(6),
3 source_id NUMBER,
4 load_date DATE,
5 data_size NUMBER,
6 state VARCHAR2(20))
7 PARTITION BY RANGE (data_size)
8 ( PARTITION data_01_2009 VALUES LESS THAN (10) TABLESPACE DATA_01,
9 PARTITION data_02_2009 VALUES LESS THAN (20) TABLESPACE DATA_02,
10 PARTITION data_03_2009 VALUES LESS THAN (30) TABLESPACE DATA_03,
11 PARTITION data_04_2009 VALUES LESS THAN (MAXVALUE) TABLESPACE DATA_04
12 );
Table created.

Inserting some data into the table:

SQL> insert into DATA_LOAD values (1,1,sysdate-20,9,'DL');
1 row created.
SQL> insert into DATA_LOAD values (2,2,sysdate-9,35,'RJ');
1 row created.
SQL> insert into DATA_LOAD values (3,3,sysdate-8,45,'LL');
1 row created.
SQL> insert into DATA_LOAD values (4,4,sysdate-7,100,'KK');
1 row created.
SQL> insert into DATA_LOAD values (5,5,sysdate-6,200,'JJ');
1 row created.
SQL> commit;
Commit complete.
SQL> analyze table DATA_LOAD compute statistics;
Table analyzed.
SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,num_rows from user_tab_partitions where TABLE_NAME='DATA_LOAD';
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
--------------- --------------- --------------- ----------
DATA_LOAD DATA_01_2009 10 1
DATA_LOAD DATA_02_2009 20 0
DATA_LOAD DATA_03_2009 30 0
DATA_LOAD DATA_04_2009 MAXVALUE 4

Now we can see that the 4th partition has more rows than the the others, so lets try and split the partition in to 2. And lets make a partition from value 30 to 100.

SQL> alter table DATA_LOAD split partition data_04_2009 at (100) into (PARTITION data_04_2009_a, PARTITION data_04_2009_b);
Table altered.

SQL> analyze table DATA_LOAD compute statistics;
Table analyzed.

SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,num_rows from user_tab_partitions where TABLE_NAME='DATA_LOAD';
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
--------------- --------------- --------------- ----------
DATA_LOAD DATA_01_2009 10 1
DATA_LOAD DATA_02_2009 20 0
DATA_LOAD DATA_03_2009 30 0
DATA_LOAD DATA_04_2009_A 100 2
DATA_LOAD DATA_04_2009_B MAXVALUE 2

So you can see the last partition got split into 2 parts.

Some important things to keep in mind before doing a split partition:

1.) SPLIT PARTITION cannot be used for hash partitions or subpartitions.
2.) Partition cannot be split along the already specified high boundry of the partition you are trying to split.

A good metalink note: 199623.1

Details about Oracle Partition Methods/Types