Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Archive Deleted Rows

Re: Archive Deleted Rows

From: Mark D Powell <mark.powell_at_eds.com>
Date: 1 Aug 2002 07:08:51 -0700
Message-ID: <178d2795.0208010608.502990c2@posting.google.com>


adimayuga_at_yahoo.com (Adrienne) wrote in message news:<ee790b43.0207311358.20c47b3e_at_posting.google.com>...
> Hello -
> We are looking into a way to create archive tables for records that
> have been deleted off of a table. Currently, when records are deleted
> we set a field called "activation_status" to inactive. The main reason
> why we decided that is to be able to look back as to what old data,
> and not deleting the record. Our thoughts to this so far have been to
> create a trigger on the table to automatically create a record in an
> archive table when a record is deleted. Are there any other
> suggestions or other ways to accomplish an archive of deleted records?
> I'm not an Oracle expert at all, so any help would be much
> appreciated.
>
> Thanks in advance.

Using trigger to copy deleted rows to a history table or archive table is common method. I consider the difference to be that a history table and its data hang around while archive data would normally be copied off to tape, CDs, or some other storage and removed from Oracle.

If you are going to remove the data then instead of using a trigger you can just write a program that reads the tables, writes an OS file of the inactive rows changed to inactive X days ago (meaning you want a status change date or other date column you can key against also) and on successful close of the OS file then deletes these rows from the Oracle table.

And you can mix variations of the above such as inactive rows are copied to history and deleted on a monthly basis leaving on current data in the main table. The deleted rows are available in history for 1 to 3 years or some appropriate time period, and then either just discarded or archived off to an external storage media.

HTH -- Mark D Powell -- Received on Thu Aug 01 2002 - 09:08:51 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US