RE: Determine Record Creation Date Without Audit Turned On
Date: Fri, 20 Jun 2008 16:24:32 +0100
Message-ID: <DC1C589CC7F042CE976DB50F0B131312@regulus>
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-lReceived on Fri Jun 20 2008 - 10:24:32 CDT