SQL & PL/SQL

Hi All,
We do have a requirement in my current assignment. We have two set of tables

We want to achieve this functionality using ORACLE partitions only. We are using 10g database & may receive millions of records for a week.

One set called TABLE_CURR -Current tables contain last four weeks (Nov wk4, Nov wk3, Nov wk2 & Nov wk1) data,

Other set called TABLE_HIST -History tables contain last 52 weeks data (Oct Wk4, Oct wk4, Oct wk2, Oct wk1, Sept 4...Etc,,,,upto 52 weeks).

When i received next week data (Let say Dec Wk1), I need to move Nov w4 (Which is last week ) data from TABLE_CURR to TABLE_HIST table.

I do have approach using Partitions EXCHANGE,,,But what it does, It will move all the records from TABLE_CURR to TABLE_HIST. I don't move all records from TABLE_CURR. I want move last week data into my History table.




CREATE TABLE TABLE_HIST (
id NUMBER,
description VARCHAR2(50),
DOB date)
PARTITION BY RANGE (DOB)
(
PARTITION P111 VALUES LESS THAN (TO_DATE('DEC-2008','MON-YYYY')),
PARTITION p122 VALUES LESS THAN (MAXVALUE)
);



ALTER TABLE TABLE_HIST
EXCHANGE PARTITION p122
WITH TABLE TABLE_CURR
WITHOUT VALIDATION;



TABLE_CURR not allowed any partition; Because of ALTER TABLE EXCHANGE requires a non-partitioned, non-clustered table

Be grateful for your help.

Keenly I’m waiting for your suggestions.

Thank You