Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Archiving Data

Re: Archiving Data

From: DA Morgan <>
Date: Tue, 04 Jul 2006 14:01:58 -0700
Message-ID: <> wrote:
> 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

I would suggest that you contact your Oracle sales rep's technical counterpart and ask for a review of built-in features you've already paid for. Here are a few items to consider:

  1. RMAN (you should already be using it)
  2. Streams
  3. Change Data Capture
  4. Data Guard

It is generally a waste of time to reinvent the wheel. ;-)

Daniel A. Morgan
University of Washington
(replace x with u to respond)
Puget Sound Oracle Users Group
Received on Tue Jul 04 2006 - 16:01:58 CDT

Original text of this message