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@xxxxxxxxxxxxxxxxxxxx> 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@xxxxxx] > *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@xxxxxxxxxxxxxxxxxxxx] > *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@xxxxxxxxxxxxx [mailto: > oracle-l-bounce@xxxxxxxxxxxxx] *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@xxxxxxxxx] > *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@xxxxxx> > 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 > >