You Asked

Hi Team,

We have a partitioned table having 1-1 week partition created using range and Interval partitioning.
first partition name is defined but rest all the partition are creating dynamically(because of INTERVAL) and having system generated partition names.

I want to drop last 2 week partition but in case of INTERVAL partitioning system generated names does not have anything in common. how to find out the correct partition name to be dropped?

Moreover I want to drop this partition in a dynamic script such that I provide only a table_name and it will automatically find out that table's last 2 week partition and drop it.

Whatever table name I provide script should drop that table's last 2 week partition.

How can this be possible?

and Chris said...

The PARTITION_POSITION in *_TAB_PARTITIONS orders the partitions by high_value, lowest to highest. Starting at one.

So if you want to drop the oldest partition, you can query this to get the partition name. Then pass this to some dynamic SQL to remove it.

Here's a (non-dynamic) example:

create table t (
  c1 
) partition by range ( c1 ) 
  interval ( 2 ) (
    partition p0 values less than ( 1 ) 
  )
as
  select level c1 from dual
  connect by level <= 10;
  
select partition_name, high_value  
from   user_tab_partitions
where  table_name = 'T'
and    partition_position = 1;

PARTITION_NAME    HIGH_VALUE   
P0                1 

alter table t
  drop partition p0;
  
select partition_name, high_value 
from   user_tab_partitions
where  table_name = 'T'
and    partition_position = 1;

PARTITION_NAME    HIGH_VALUE   
SYS_P571          3    

alter table t
  drop partition SYS_P571;
  
select partition_name, high_value
from   user_tab_partitions
where  table_name = 'T'
and    partition_position = 1;

PARTITION_NAME    HIGH_VALUE   
SYS_P572          5

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Example doesn't work: ORA-14758: Last partition in the range section cannot be dropped

Dieter, December 08, 2022 - 9:12 pm UTC

Chris, I tried your example, because I couldn't believe it would work.

I'm using a 12.1 as well

create table t (
  c1 
) partition by range ( c1 ) 
  interval ( 2 ) (
    partition p0 values less than ( 1 ) 
  )
as
  select level c1 from dual
  connect by level <= 10;

Table T created.
 
select partition_name, partition_position, high_value , interval 
from   user_tab_partitions
where  table_name = 'T'
and    partition_position = 1;

PARTITION_NAME PARTITION_POSITION HIGH_VALUE INTERVAL
-------------- ------------------ ---------- --------
P0                              1 1          NO      

alter table t
  drop partition p0;

Error report -
ORA-14758: Last partition in the range section cannot be dropped
14758. 00000 -  "Last partition in the range section cannot be dropped"
*Cause:    An attempt was made to drop the last range partition of an interval
           partitioned table.
*Action:   Do not attempt to drop this partition.

Did I do something wrong?
However, I would like to just drop the oldest partition and use the remaining oldest INTERVAL partition as new starting point instead. Is there any easy way to do so?

December 09, 2022 - 3:59 am UTC

This is solved in 12.2, but you can see how to handle it in earlier releases here

Dieter, December 09, 2022 - 9:06 am UTC

Thanks, it works this way :-)

To complete it here for the testcase above in case of still using 12.1:
In order to be able to drop the oldest and last range partition, we need to execute the interval command once again. This converts all INTERVAL partitions to RANGE Partitions and so we have again more than 1 RANGE partition.

ALTER TABLE T SET INTERVAL (2);

Disadvantage of this is only, that in case of gaps between interval partitions, they might get converted to bigger ranges than they were intended before. But in most cases, it won't be a problem.

December 12, 2022 - 3:28 am UTC

glad we could help

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Example doesn't work: ORA-14758: Last partition in the range section cannot be dropped

More to Explore

VLDB

VLDB

Drop oldest interval partition dynamically

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Example doesn't work: ORA-14758: Last partition in the range section cannot be dropped

More to Explore

VLDB

VLDB