Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Keeping deleted records

Re: Keeping deleted records

From: Stefan Knecht <>
Date: Thu, 9 Aug 2007 19:38:47 +0200
Message-ID: <>


How about adding a column "DELETED" to the tables, and mask the original structure using views and instead of triggers, that just flag the new field instead of a delete.


On 8/9/07, Bill Ferguson <> wrote:
> Hi all,
> My management wants to keep a copy of all deleted records for historical
> purposes, in case somebody decides at a later point that the deletion was a
> mistake. This happens about once a year for probably a dozen or so records.
> The data structure is about 35 tables consisting of one master table with
> children attached to it. Only two of the child tables have their own
> children. This primarily a scientific record type database, of known
> worldwide mineral deposits, so the volume of transactions is fairly low,
> compared to what most of you are used to dealing with. I might have a couple
> hundred transactions per day on a busy day.
> I'm kind of torn between two approaches. The first approach is to
> basically create a duplicate schema that contains blank table structures,
> and as records are deleted, move them over to the "deletes" schema and then
> delete them from production. This will entail of bunch of redesign though on
> all of the triggers and others constraints in the new schema.
> The second approach would be to have the "deletes" schema be a copy of
> everything currently in the production schema, and then as new records are
> added, add them to the "deletes" schema, and if they're deleted, then I
> don't have to do anything. This approach though would entail the reworki9ng
> of all of the existing triggers in the production schema.
> Either way, management hasn't said what kind of tracking (if any) that
> they want for updates. It seems that they'd probably want that as well, so
> if a production record was updated, the new version of the record would be
> copied over to the "deletes" schema.
> Anybody else ever run across this kind of requirement before and have any
> ideas or suggestions on the best/easiest way to handle it?
> RMAN backups would really be a pain, as the deletes could have occured at
> any time and over a huge period of time as well.
> Thanks.
> --
> -- Bill Ferguson


Stefan P Knecht
Infrastructure Managed Services

Trivadis AG
Europa-Strasse 5
CH-8152 Glattbrugg

Phone +41-44-808 70 20
Fax +41-808 70 12
Mobile +41-79-571 36 27


Received on Thu Aug 09 2007 - 12:38:47 CDT

Original text of this message