You can not use the DML where clause with DDL. Dynamic SQL will be
needed if you inisist on dropping the partitions this way. Build an
anonamous block, query the user_tab_partitions to find the partitions
you wish to drop and build the dynamic SQL to drop the partitions.
nhourdou_at_amadeus.net (nh) wrote in message news:<747d5f3b.0303270758.7f6fe11e_at_posting.google.com>...
> Hi there!
> I have a partitionned table by date:
>
> > create table T (d date, i integer) partition by range(d) (partition pi
> values less than (to_date('01012003','ddmmyyyy')));
>
> Each partition is huge (about 100.000 lines) and concerns a particular
> day.
>
> Then I store data for 370 days.
>
> So each day I add a partition and drop a 370 days old one...
>
> I wish to know if there is a way to drop several partitions with query
> like:
> >alter table T drop partition pi where d < aDate;
>
> Indeed if I drop only the 370 days old partition (and not 370 and more
> years old partition), if one day I do not execute this query the
> partition will still exist...
>
> e.g:
>
> T:
> d i
> 01012001 20 partition p01012001
> 02012001 29 partition p02012001
> 03012001 30 partition p03012001
>
> I wish to delete both p01012001 and p02012001...
>
> In real case I do not know which partition exists before date d...
>
> Many thanks...
Received on Thu Mar 27 2003 - 12:21:11 CST
