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

ORA-14074: partition bound must collate higher than that of the last partition

Problem:

ORA-14074: partition bound must collate higher than that of the last partition

What was being done:

I had a table which had daily partitions. I wanted to drop daily partitions and create weekly partitions. I have the data available elsewhere, so I can drop the partitions. I had dropped partitions for a week:

alter table apun.log drop parition PARTITION_2007_10_01;
alter table apun.log drop parition PARTITION_2007_10_02;
alter table apun.log drop parition PARTITION_2007_10_03;
alter table apun.log drop parition PARTITION_2007_10_04;
alter table apun.log drop parition PARTITION_2007_10_05;
alter table apun.log drop parition PARTITION_2007_10_06;
alter table apun.log drop parition PARTITION_2007_10_07;
alter table apun.log drop parition PARTITION_2007_10_08;

and tried creating weekly partition:

SQL> ALTER TABLE apun.log ADD PARTITION week_01_10_2007 VALUES LESS THAN ((TO_DATE('2007-10-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')))
NOLOGGING
COMPRESS
TABLESPACE log_tablespace;
PARTITION week_01_10_2007 VALUES LESS THAN ((TO_DATE('2007-10-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')))
*
ERROR at line 3:
ORA-14074: partition bound must collate higher than that of the last partition

I still have other daily partitions, after the ones I dropped.

Resolution:

Searched the net and metalink and found that what needs to be done is to use SPLIT PARTITION:

ALTER TABLE apun.log
SPLIT PARTITION PARTITION_2007_10_09 AT
(((TO_DATE(' 2007-10-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))))
INTO (week_01_10_2007
TABLESPACE log_tablespace
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 128M
MINEXTENTS 1
PCTINCREASE 0
BUFFER_POOL DEFAULT
),
PARTITION PARTITION_2007_10_09);