Using truncate partition on running table!!

From: Nish <nishant.aggarwal_at_gmail.com>
Date: 8 Mar 2005 03:52:37 -0800
Message-ID: <1110282757.966085.76150@z14g2000cwz.googlegroups.com>

Hi All,
I am having a problem which requires me to manage a table, which is handling 500 entries/sec.Once 24 hours are passed,I am required to take the backup of oldest 1 hour and clean that data also.From now on,every 1 hour I am required to take backup of the oldest 1 hour and so forth.So that at any point of time, I have the latest 24 hours of data. I tried to handle the above situation by making 24 range partitions based on "hour of day".A cronjob is run which after 24 hours "truncates the partition" containing the oldest 1 hour data. Also, data insertions are also continuing at 500 entries/sec. I get the following exception while inserting data,

---

"EXCEPTION :ORA-01502: index 'EVENT_IDX' or partition of such index is in unusable state"

---

The cronjob basically does the following,

---

REM Partition of the hour 17
ALTER TABLE EVENT_DETAILS DISABLE PRIMARY KEY ; ALTER TABLE EVENT_DETAILS TRUNCATE PARTITION EVENT_REPORTS_17; DROP INDEX EVENT_IDX ;
CREATE INDEX EVENT_IDX ON EVENT_DETAILS(GEN_EVT_ID) ; ALTER TABLE EVENT_DETAILS ENABLE PRIMARY KEY ;

---

There is no other constraints except for one primary key and some not null constraints on the table.

Can you please help me out on this problem?

Any help would be greatly appreciated!!
Thanks in advance,
Nish. Received on Tue Mar 08 2005 - 05:52:37 CST