Archiving Data

Hi,

I am trying to come up with a solution to automatically archive data in our Oracle database.

Our scenario is as follows:

We have 35 million records coming in per month into our database. Every month we create a new table to store the records for that month.

Since 35 million records is quite a few, we'd like to only store 1 or 2 months in our database, and then automatically run a script or something that would copy out older tables into another database which we'd use to run queried reports on. The problem with this is you are going to have to copy 35 million records at the end of every month. Is this going to be a performance problem on the CPU while the copy is taking place?

Another solution had thought of was to create a new database every X months to store new incoming data. All old databases would be archived databases. The only problem with this is it seems difficult to create new databases via script, plus in 5 years you are going to have 20+ databases kicking around.

What is the best idea for achieving something like this? Is there any administrative tools like SQL's DTS that might do some of this for me?

I don't know enough about database administration to come up with the best solution.
I'm just the programmer implementing this archiving problem.

We're currently using C# ADO.NET 1.1 and Oracle 10g.

Thanks in advance for the input,
Kevin Received on Tue Jul 04 2006 - 15:54:47 CDT