Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

Re: Keeping deleted records

From: August Spier <>
Date: Thu, 9 Aug 2007 14:08:51 -0400
Message-Id: <>


In broad brush strokes ...

Add one column to each of the tables, one named "DELETED_DTG" with datatype of DATE. Build a view of the tables without the DELETED_DTG column WHERE DELETED_DTG IS NULL. Give the user community access to the views. Write a procedure that populates the DELETED_DTG column of each record the end user tries to delete with SYSDATE. Refresh as needed/expedient.

Set the role for all users to use your new DELETE_RECORD procedure, reserve permission to DELETE RECORDS for DBAs or the onwer of the data, and Bob's your uncle.

Now your users can delete to their heart's content, your data owner can "undelete" as desired, and you can save the day with alarming regularity.



On Aug 9, 2007, at 12:31 PM, 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

Received on Thu Aug 09 2007 - 13:08:51 CDT

Original text of this message