Help !!!

[hedyash]

Hi,

I have around 70 tables all referenced to a main table with a key. I need to archive all the table(s) data every month. At any point of time in a month, there will be 3 months worth of data available. When requested I should be able to restore the data for the previous month(s), based on the request.

How can I go about doing this? How do I archive just 1 months data from all tables. As far as I remember there is no date field in the main table.

Any suggestions on this is welcome.

Thanks in advance.

[LND]

non effective but simple solution is to shedule delete of old data (<3 month) from main tables to archive tables every day. That way you minimize load on system. The deletes should be sheduled when system is least loaded with other tasks.
(I assume you have date field to find what data is 3 month old somehwere in the tables, otherwise you need to add such column. Adding new column such that it is transparent to application is a different story)

The other way to deal with archiving is to have partitions, however care about foreign keys must be taken. If system is not 7*24 then it is quite easy taskto do.

[hedyash]

Thank you.

I was able to get the insert into archive tables and delete from base tables to work. I guess it would be better to do it using partitions.

Can I create partitions based on the partial information in the column. (e.g. - substr(column....))

Thanks again
Hedy