Split Partition Fails with ORA-04021 AND ORA-14080: partition cannot be split along the specified high bound (Doc ID 2241745.1)

Last updated on APRIL 17, 2023

Applies to:

Symptoms

Split partition failed with ORA-14080 after ORA-04021

Problem Description
---------------------------------------------------
ois_dom  is a range partitioned table. The partition key is INS_TS TIMESTAMP(6).

select PARTITION_NAME , partition_position , high_value from user_tab_partitions where table_name = '&TABLE_NAME' order by partition_position;

P_201602 1 TIMESTAMP' 2016-03-01 00:00:00'
P_201603 2 TIMESTAMP' 2016-04-01 00:00:00'
P_201604 3 TIMESTAMP' 2016-05-01 00:00:00'
P_201605 4 TIMESTAMP' 2016-06-01 00:00:00'
P_201606 5 TIMESTAMP' 2016-07-01 00:00:00'
P_MAXVALUE 6 MAXVALUE

You  are trying to split data in p_maxvalue partition to the proper monthly partitions.

The split times  out after  hours with ORA-04021: timeout occurred while waiting to lock object.
Rerunning the statement returns ORA-14080: partition cannot be split along the specified high bound.



09:58:31 SQL> alter session set ddl_lock_timeout=60;

Session altered.
09:58:31 SQL> alter table ois_dom  split partition p_maxvalue at (TO_DATE('01-AUG-2016','dd-MON-yyyy')) into (partition p_201607, partition p_maxvalue );
alter table ois_dom
  *
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object

Elapsed: 05:55:43.33

16:09:33 SQL> alter table ois_dom
16:10:14 2 split partition p_maxvalue at (TO_DATE('01-AUG-2016','dd-MON-yyyy')) into (partition p_201607, partition p_maxvalue );
split partition p_maxvalue at (TO_DATE('01-AUG-2016','dd-MON-yyyy')) into (partition p_201607, partition p_maxvalue )
  *
ERROR at line 2:
ORA-14080: partition cannot be split along the specified high bound

Using TO_DATE('01-08-2016','dd-mm-yyyy') fails with same error.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.