- 17 Dec, 2023
- 0 Comments
- 2 Mins Read
ORA-14074: Partition Bound Must Collate Higher Than That Of The Last Partition.
Problem :
While adding a partition to a partitioned table, we got this error.
To avoid this error, the need to split the partition, instead of adding.
1 2 3 4 5 6 7 8 | SQL> select partition_name,high_value from dba_tab_partitions where table_name='DBACLASS_QTAB';PARTITION_NAME HIGH_VALUE-------------- --------------------------------------------------------------------------------DBACLASS_QM MAXVALUEDBACLASS_Q3 TO_DATE(' 2023-03-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIADBACLASS_Q2 TO_DATE(' 2023-04-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIADBACLASS_Q1 TO_DATE(' 2023-06-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA |
Here we were trying to add a partition with high_value 03-APR-2023, which is between partition DBACLASS_Q3 and DBACLASS_QM. So in this case for adding a new partition we need to split the maxvalue partition.
1 2 3 4 5 6 7 8 9 10 11 12 13 | alter table dbaclass_QTAB SPLIT PARTITION DBACLASS_QMAT (TO_DATE('03-APR-2023','DD-MON-YYYY'))INTO ( PARTITION DBACLASS_Q4,PARTITION DBACLASS_QM);SQL> select partition_name,high_value from dba_tab_partitions where table_name='DBACLASS_QTAB';PARTITION_NAME HIGH_VALUE-------------- --------------------------------------------------------------------------------DBACLASS_QM MAXVALUEDBACLASS_Q4 TO_DATE(' 2023-02-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIADBACLASS_Q3 TO_DATE(' 2023-03-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIADBACLASS_Q2 TO_DATE(' 2023-04-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIADBACLASS_Q1 TO_DATE(' 2023-05-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA |