Automatically maintaining a history database
Date: Wed, 12 Aug 1998 08:22:26 -0500
Message-ID: <35d1af1a.0_at_209.136.2.9>
We are currently using the workgroup database SQL Anywhere and are porting to Oracle to handle our larger clients. We need to maintain an active and history database. The active maintains a roughly constant size because inactivated (by the user) rows older than a configurable amount of time are deleted. The history database is the same database but without any of the deletes.
We implement this in SQL Anywhere by starting with two empty databases, one callled active and the other called history. Before the daily backup is done all the old records are deleted from the active database. After the backup, we take the transaction log from the active database (with one day's worth of transactions) and use a SQLA utility to translate the log to SQL, filtering out all the deletes of the older records (the utility has an option to filter transactions by a given user, we have a userid that does nothing but these deletes). We then apply this sql to the history database. The user then can work in the active database which has just the recent information and is faster because it is much smaller and be able to log into the history database to see everything that has ever happened. The history database is primarily for auditing purposes and to run reports that show historical trends.
Does Oracle have similar options with something like a transaction log? If not does anyone have ideas as to how we can implement this same idea using Oracle?
-- Travis L. Norwood Health Care Systems, Inc.Received on Wed Aug 12 1998 - 15:22:26 CEST