Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Boshoff.

Asked: June 19, 2018 - 9:23 am UTC

Last updated: June 20, 2018 - 10:07 am UTC

Version: 11.2.0.3

Viewed 1000+ times

You Asked

Hi
I have a table that was created for debugging purposes. Every night a jobs kicks off creating a partition of the days inserts on the table based on date.
Needless to say have the partitions grown rapidly and have taken up a lot space in the tablespace.

What is the best way of dropping/deleting the partitions and freeing up the space in the table space to only reflect the last months worth of rows/partitions for this table ?
There are now indexed partitions on the table.
If there were indexed partitions for every partitions, are they also dropped when you drop the partition?


See below part of the procedure that creates the partition every night :

part_action := 'alter table DEBUG add partition DEBUGPART' || to_char(sysdate + 2 , 'YYYYMMDD') ||' values less than (TO_DATE(''' || to_char(sysdate + 3 , 'YYYYMMDD HH24:MI:SS') || ''',''YYYYMMDD HH24:MI:SS'')) tablespace DATA';

Regards
Boshoff Smit

and Chris said...

All you need to do is drop the partition!

Any local indexes will also go in the process. But it will invalidate any global indexes:

create table t (
  x, stuff, constraint pk primary key ( x ) 
) partition by range (x) (
  partition p0 values less than (250),
  partition p1 values less than (500),
  partition p2 values less than (750),
  partition p3 values less than (1000)
) as 
  select level x, lpad('x', 1000, 'x') stuff 
  from   dual
  connect by level < 1000;
  
select partition_name, bytes
from   user_segments
where  segment_name = 'T';

PARTITION_NAME   BYTES    
P0                 393216 
P2                 393216 
P1                 393216 
P3                 393216

alter table t drop partition p0 ;

select partition_name, bytes
from   user_segments
where  segment_name = 'T';

PARTITION_NAME   BYTES    
P2                 393216 
P1                 393216 
P3                 393216 

select index_name, status
from   user_indexes
where  table_name = 'T';

INDEX_NAME   STATUS     
PK           UNUSABLE 


Meaning you need to rebuild them afterwards.

You can drop & rebuild in one step with the "update indexes" clause:

alter index pk rebuild;
alter table t drop partition p1 update indexes;

select partition_name, bytes
from   user_segments
where  segment_name = 'T';

PARTITION_NAME   BYTES    
P2                 393216 
P3                 393216 

select index_name, status
from   user_indexes
where  table_name = 'T';

INDEX_NAME   STATUS   
PK           VALID   


This allows you to keep using the application while you're dropping the partition. But it can be quite resource intensive.

There was a tweak to this behaviour in 12c: Asynchronous Global Index Maintenance. This defers the index maintenance to a later date, speeding up the drop.

https://docs.oracle.com/database/121/VLDBG/GUID-087B87A6-959A-40C6-82AF-36E401FD089B.htm#VLDBG14107

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

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database