Managing Partitions - using Range Partitions concept

[Anna Purna]

Hi Everyone,

In my schema there is a Table "CALL_HISTORY" is Range partitioned on Call_date column. we are creating 6 partitions per day. When partitions are huge in number, we are backing up the some of the partitions and dropping them. Before dropping we are inserting these partition details into some temporary table with the details like Name of the Table, Name of the Partition, Partition value which will be useful for adding these partition later.

Let say "CALL_HISTORY" table contains the partitions
p_min,part_date_1,part_date_2,part_date_3,part_date_4,p_max.

we can't drop p_min and p_max partitions.

If I dropped part_date_1,part_date_2 partitions, then we need to change partition value of p_min in such way that it should cover the date ranges of part_date_1,part_date_2. When part_date_1 added back, then p_min partition value adjusted in such way that its partitiion value should be less than part_date_1 partition value. Can any body help me how to implement this logic through PLSQL procedure.

Thanks in advance

Thanks,
Anna Purna

[slimdave]

Do you mean that you don't want to drop p_min and p_max, rather than you can't?

How about using partition exchanges on part_date_1 and part_date_2, thus getting their data into individual tables and emptying the partitions? Do you then need to drop the empty partitions, or could you just leave them empty? If you drop them then want to add back in the other partitions then you'll have to split it, which is simple to do but requires a bit of work on Oracle's part.

[Anna Purna]

Hi slimdave,

Thank you for ur quick reply. Once I drop the partitions, I need to add back after some time whenever needed to the same table without data means empty partitions only. When adding empty partitions I need to split existing partitions including P_MIN(P_MAX not required). Can u help to how to write the programme for this. Suggest some links with programming related to this. I am first time working on splitting concept of partitioning.

Thanks a lot in advance,

Regards,
Anna Purna

[slimdave]

Why don't you just leave the empty partitions there?

Anyhoo, the code for partition operations is under "alter table" in the SQL Reference.