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

SQL> ALTER TABLE MGR.SAVE_MT ADD PARTITION 2023M04 VALUES LESS THAN (TO_DATE(' 2023-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
ALTER TABLE MGR.SAVE_MT ADD PARTITION 2023M04 VALUES LESS THAN (TO_DATE(' 2023-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
                                                       *
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition

Cause - Partition bound specified in ALTER TABLE ADD PARTITION statement did not collate higher than that of the table's last partition, which is illegal.

Check partition

SQL> select table_name, partition_name, high_value from dba_tab_partitions where table_name='SAVE_MT' and table_owner ='MGR'

TABLE_NAME           PARTITION_NAME  HIGH_VALUE
-------------------- --------------- ------------------------------------------------------------------------------------------
...
SAVE_MT              P2023M03        TO_DATE(' 2023-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SAVE_MT              PMAX            MAXVALUE
SAVE_MT              PMIN            TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

add partition using SPLIT PARTITION

-- No Parallel
SQL> alter table MGR.SAVE_MT split partition PMAX at (TO_DATE(' 2023-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) into (partition P2023M04 ,partition PMAX);

Table altered.

-- Or With Parallel and Update Global Index
SQL> alter table MGR.SAVE_MT split partition PMAX at (TO_DATE(' 2023-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) into (partition P2023M04 ,partition PMAX) UPDATE GLOBAL INDEXES PARALLEL 16;

Check partition

SQL> select table_name, partition_name, high_value from dba_tab_partitions where table_name='SAVE_MT' and table_owner ='MGR'

TABLE_NAME           PARTITION_NAME  HIGH_VALUE
-------------------- --------------- ------------------------------------------------------------------------------------------
...
SAVE_MT              P2023M03        TO_DATE(' 2023-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SAVE_MT              P2023M04        TO_DATE(' 2023-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SAVE_MT              PMAX            MAXVALUE
SAVE_MT              PMIN            TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

張貼留言

0 留言