CODEWITHSUNDEEP
oracle-database
Manimalika
Manimalika

Reputation: 1

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

I am getting this error.

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

I am unable to solve this error.

I am trying to adding partition to the table.

Upvotes: 0

Views: 4902

Answers (1)

Chris Saxon
Chris Saxon

Reputation: 9775

The limit value for the partition you're adding is lower that the highest limit already in the table:

create table t (
  c1 int
) partition by range ( c1 ) (
  partition p1 values less than ( 9 ),
  partition p2 values less than ( 99 )
);

alter table t 
  add partition p3 values less than ( 0 );
  
ORA-14074: partition bound must collate higher than that of the last partition

0 < 99 => you can't add the partition. If you want a partition with this upper bound you'll need to split an existing partition.

You can find the current highest value by querying *tab_partitions and getting the high_value for the partition with the max partition_position

with rws as (
  select table_name, partition_position, high_value, 
         row_number () over ( 
           partition by table_name 
           order by partition_position desc 
         ) rn
  from   user_tab_partitions
)
select table_name, high_value 
from   rws
where  rn = 1;

TABLE_NAME HIGH_VALUE                                                                      
---------- ----------
T          99

Upvotes: 1

Related Questions