Hi,
I am searching in google for a pl/sql script that will purge old partitions and insert a new partition based on the date. (ie. keep 24 months, each month insert a new partition and delete the old partition(24),

I have not found anything in google and thought maybe someone might have a similar script that I can use as a base.

Your help is greatly appreciated.

Thanks,

07-25-2007, 10:24 AM

PAVB

Assuming from your posting you are resorting to range-partitioning...

You do not insert a new partition, you usually split the max-value partition.
You do not delete a partition, you either truncate it or drop it.

Ora10g might help you creating new partitions as needed but, don't be lazy, you can write the script. Just google "alter table drop partition" and "alter table split partition"

07-25-2007, 10:33 AM

mike9

Pascal,

Is it realy so hard to read the Oracle documentation and to write 2 lor 3 lines of code ???

Hi.
"Is it realy so hard to read the Oracle documentation and to write 2 lor 3 lines of code ???" Mike9, I had no problem creating the partitioned table, that was not the issue for me. It was writing the pl/sql to loop and remove the oldest partition.

Liakat. Thanks for the terrific example. I will use it as my baseline. Much appreciated.

Cheers.

07-26-2007, 12:37 PM

liakat

Quote:

Originally Posted by PAVB

No it doesn't. It drops partition based on partition_name.

SORRY, YES It drops partition based on partition_name.
I thing i need more carefull.