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