Periodic management of Oracle10g partitions!

From: Nish <nishant.aggarwal_at_gmail.com>
Date: 14 Jan 2005 05:41:58 -0800
Message-ID: <1105710118.390752.255410@f14g2000cwb.googlegroups.com>

Hi All,
I am using Oracle10g on HP-UX 11.23(Itanium)system. I am having some trouble figuring out a problem , in which I need to keep inserting data at the rate of 3000 rows/sec in one table and also backup old data. I plan to do range partitioning of the table using a date or primary key(generated using a sequence).Also,there would be a script running as cron-job which would check on the partitions status and when the "last" partition starts getting filled, backups the data of the old partitions.

But,the issue is that both date and sequence-id are increasing in one direction, so when I backup one of the partition, the condition for say, "date" remains "unchanged", ie,the data will always be filled in
"part3"(last partition) as mentioned below.

The partition sample sql being say for "date",

---

create table t(id number(20),name varchar2(20),mydate date) partition by range (mydate)(
partition part1 values less than (add_months(sysdate,+1)) tablespace users,
partition part2 values less than (add_months(sysdate,+2)) tablespace users,
partition part3 values less than (MAXVALUE) tablespace users );

---

Now, when the data starts getting filled in "part3", I would like
"part1" and "part2" to be backed up and after backup,the new data to
start getting inserted in "part1" instead of "part3".

Can you please suggest some way in which this can be handled or is there a better way of handling such situation?

Thanks & Regards,
Nish. Received on Fri Jan 14 2005 - 07:41:58 CST