Skip to Main Content
  • Questions

  • Drop oldest interval partition dynamically

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Connor McDonald

Thanks for the question, Yam.

Asked: August 12, 2019 - 11:08 am UTC

Last updated: December 12, 2022 - 3:28 am UTC

Version: 12.1

Viewed 10K+ times! This question is

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?
Connor McDonald
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.
Connor McDonald
December 12, 2022 - 3:28 am UTC

glad we could help
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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.