Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: archive old data

Re: archive old data

From: Pete Finnigan <>
Date: Tue, 16 Sep 2003 05:54:33 -0800
Message-ID: <>

Hi Nancy

One way we did it three / four years ago whilst working on a finance system was to build a second "archive" database. The main database table in the production system held details of the financial transactions and was partitioned into 13 partitions split on months, at each month end the oldest one was exported off and removed from live and a new partition was "opened" for the next month.

There was a requirement that archived data needed to be available but didn't need to be online, so we built a second database with just the finance transactions table with six empty partitions. All of the other tables in the live system didn't get archived as they were either lookup  tables or small enough that the old data didn't impact the performance and storage from a users perspective. This helped us as the "archive" database then had a set of a few hundred synonyms that were links to the tables in the live system.

When archived data was needed by the business it was imported to the archive partition's and users pointed the application at this database. The access times were impacted by this method but our remit on providing archived data on this legacy system was to save costs on disk space and hardware. We scripted the rollover so it happened automatically at month end. The solution was not perfect but it worked and saved disc space in not needing to replicate the whole database and not having to archive all of the data and allowed recovery of archived data quite quickly. The production database held 1.5 - 2 terabytes of data by the way mostly in the financial transactions.


kind regards


Pete Finnigan
Web site: - Oracle security audit specialists Book:Oracle security step-by-step Guide - see for details.


Please see the official ORACLE-L FAQ:

Author: Pete Finnigan

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message to: (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Sep 16 2003 - 08:54:33 CDT

Original text of this message