Re: Oracle logical delete concept question
From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Tue, 17 Mar 2009 15:03:10 -0700 (PDT)
Message-ID: <495e59e4-d97b-4514-acf4-b11b7463fc20_at_j38g2000yqa.googlegroups.com>
On Mar 16, 11:25 am, "Larry W. Virden" <lvir..._at_gmail.com> wrote:
> I was asked to look into the concept of oracle's support of logical
> deletes for rows.
>
> The background is that the current set of tables are small, but
> requests to have them restored can take a while to complete, and also
> one would lose any modifications that occurred since the last backup.
>
> In a discussion about the situation, the topic of Oracle support for
> logical deletes, where the row "remains" in the table, but not seen,
> came up.
>
> When I google for oracle logical deletes, I see some information about
> logically deleting columns, but the rest of the top 30-40 hits seemed
> to be irrelevant to the topic about which I am seeking.
>
> Is anyone aware of some capability in Oracle like this? If so, perhaps
> I didn't get the name correct, or complete, regarding the
> functionality.
>
> Any tips would be appreciated.
Date: Tue, 17 Mar 2009 15:03:10 -0700 (PDT)
Message-ID: <495e59e4-d97b-4514-acf4-b11b7463fc20_at_j38g2000yqa.googlegroups.com>
On Mar 16, 11:25 am, "Larry W. Virden" <lvir..._at_gmail.com> wrote:
> I was asked to look into the concept of oracle's support of logical
> deletes for rows.
>
> The background is that the current set of tables are small, but
> requests to have them restored can take a while to complete, and also
> one would lose any modifications that occurred since the last backup.
>
> In a discussion about the situation, the topic of Oracle support for
> logical deletes, where the row "remains" in the table, but not seen,
> came up.
>
> When I google for oracle logical deletes, I see some information about
> logically deleting columns, but the rest of the top 30-40 hits seemed
> to be irrelevant to the topic about which I am seeking.
>
> Is anyone aware of some capability in Oracle like this? If so, perhaps
> I didn't get the name correct, or complete, regarding the
> functionality.
>
> Any tips would be appreciated.
There are ways to version table data that are basically database independent. One method would be to create a history table and using table triggers record all changes of interest into the history table. You could query the data from there.
Another method involves placing a version indicatior on the row and placing views on top of the table (that is, query views instead of the real table) that present either the current row or a prior set of rows based on common criteria.
Oracle has a feature called workspace manager, or something close to this, that will keep multiple versions of the data. This might be an option.
HTH -- Mark D Powell -- Received on Tue Mar 17 2009 - 17:03:10 CDT