ORA-14758: Last partition in the range section cannot be dropped

An error “ORA-14758: Last partition in the range section cannot be dropped” is expected behavior while dropping the last partition in the range section. The high value of the last partition in the range section has an important role in the interval partition table. It is called the transition point where the interval partition section start, hence the last partition in the range section cannot be dropped.

Below example reproduces this error.

Creating TBL_PARTITION table for the demonstration.

SQL>create table TBL_PARTITION
 2 (id number(12),
 3 cdate date
 4 )
 5 partition by range(cdate)
 6 interval(numtoyminterval(1, 'month'))
 7 (partition p1 values less than (to_date('2000-01-01', 'YYYY-MM-DD')),
 8 partition p2 values less than (to_date('2000-04-01', 'YYYY-MM-DD')),
 9 partition p3 values less than (to_date('2000-07-01', 'YYYY-MM-DD')));

Table created.

Elapsed: 00:00:00.10

Now insert the data in the table so that automatic partitions will be created.


SQL>insert into vishal.TBL_PARTITION values(1, to_date('2000-02-15', 'YYYY-MM-DD')) ;

1 row created.

Elapsed: 00:00:00.02
SQL>insert into vishal.TBL_PARTITION values(1, to_date('2000-03-15', 'YYYY-MM-DD')) ;

1 row created.

Elapsed: 00:00:00.00
SQL>insert into vishal.TBL_PARTITION values(1, to_date('2000-05-15', 'YYYY-MM-DD')) ;

1 row created.

Elapsed: 00:00:00.00
SQL>insert into vishal.TBL_PARTITION values(1, to_date('2000-06-15', 'YYYY-MM-DD')) ;

1 row created.

Elapsed: 00:00:00.00
SQL>insert into vishal.TBL_PARTITION values(1, to_date('2000-08-15', 'YYYY-MM-DD')) ;

1 row created.

Elapsed: 00:00:00.01
SQL>insert into vishal.TBL_PARTITION values(1, to_date('2000-09-15', 'YYYY-MM-DD')) ;

1 row created.

Elapsed: 00:00:00.01
21:52:59 SQL>commit;

Commit complete.

Let’s check the partitions created for this tables.


SQL>select table_name,partition_name from dba_tab_partitions where table_name='TBL_PARTITION';

TABLE_NAME                     PARTITION_NAME 
------------------------------ ------------------------------ 
TBL_PARTITION                  P1 
TBL_PARTITION                  P2 
TBL_PARTITION                  P3 
TBL_PARTITION                  SYS_P661 
TBL_PARTITION                  SYS_P662 

Elapsed: 00:00:00.07

Partition SYS_P661 and SYS_P662 are created by interval partitioning.  Now let’s try to delete the partitions P2, SYS_P662 and P3.


SQL>alter table vishal.TBL_PARTITION drop partition SYS_P662;

Table altered.

Elapsed: 00:00:00.41
21:54:22 SQL>
SQL>alter table vishal.TBL_PARTITION drop partition P2;

Table altered.

Elapsed: 00:00:00.09
21:54:33 SQL>
SQL>alter table vishal.TBL_PARTITION drop partition P3;
alter table vishal.TBL_PARTITION drop partition P3
 *
ERROR at line 1:
ORA-14758: Last partition in the range section cannot be dropped


Elapsed: 00:00:00.16
SQL>

Partition P3 has high value while creating the table and it has important role in interval partitioning which result in the error “ORA-14758: Last partition in the range section cannot be dropped”.

References : Doc ID 882681.1

ORA-14758: Last partition in the range section cannot be dropped
Tagged on:     

Leave a Reply