Re: Determine Record Creation Date Without Audit Turned On

Since the O.P. mentioned the records in question were several years old, I feel that all business critical tables should have an audit table. A separate table with a couple of additional columns to record what change was made (insert/update/delete) and the date/time that action occurred. Put a trigger on the source table that will populate the audit table as desired with columns from the source table. This is a bit of extra work for the DBA - if you rebuild the table you have to remember to disable the trigger. However, I feel that this provides improved protection for critical business data.

Dennis Williams

On 6/20/08, David Litchfield <david_at_databasesecurity.com> wrote:
>
> With flashback it "depends" on a number of factors. It may be "quite far
> back" but then again it may not be. How busy is the server in terms of
> updates/inserts/etc? With the redo logs if archiving is enabled then you
> should have copies of older records etc... if archiving is not set then
> you'll only have 3 or so redologs and older entries get overwritten...
> HTH,
> David
>
>
> ------------------------------
> *From:* Langston, Chris [mailto:Chris.Langston_at_aa.com]
> *Sent:* 20 June 2008 16:17
> *To:* David Litchfield; Jared Still
> *Cc:* oracle-l
> *Subject:* RE: Determine Record Creation Date Without Audit Turned On
>
>
>
> How far back can you go?
>
>
>
> *From:* David Litchfield [mailto:david_at_databasesecurity.com]
> *Sent:* Friday, June 20, 2008 10:12 AM
> *To:* Langston, Chris; 'Jared Still'
> *Cc:* 'oracle-l'
> *Subject:* RE: Determine Record Creation Date Without Audit Turned On
>
>
>
> Is it not possible to use a flashback query to determine the records in
> question; or alternatively the redo logs?
>
> HTH,
>
> David
>
>
>
>
> ------------------------------
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Langston, Chris
> *Sent:* 19 June 2008 19:40
> *To:* Jared Still
> *Cc:* oracle-l
> *Subject:* RE: Determine Record Creation Date Without Audit Turned On
>
> At best I can only make the recommendation. They'll have to weigh if it's
> worth the effort to get the approvals to get it done.
>
>
>
> *From:* Jared Still [mailto:jkstill_at_gmail.com]
> *Sent:* Thursday, June 19, 2008 11:31 AM
> *To:* Langston, Chris
> *Cc:* oracle-l
> *Subject:* Re: Determine Record Creation Date Without Audit Turned On
>
>
>
> On Wed, Jun 18, 2008 at 7:44 AM, Langston, Chris <Chris.Langston_at_aa.com>
> wrote:
>
> All,
>
> We have a user that needs to do cleanup on a table in a 10.2 instance
> and wants to remove rows in a table based on when the record was created
> but there is no creation date as part of the record entry. Without
> having auditing turned on, is there a way to do determine this from the
> data dictionary tables and, if so, which ones. I'm a rather new DBA and
> not well versed in Oracle's data dictionary tables. All of my searching
> for keeps directing me to information about auditing.
>
>
> There's a simple way to set this up for future use.
>
> alter table my_table add ( row_create_date date default sysdate )
>
> Obviously this will not work for old data, but may be useful in the
> near future for cleaning up data.
>
> And 30 days from now, all rows with a null value for this column will be
> 30+ days old.
>
> --
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
>
>

--
http://www.freelists.org/webpage/oracle-l