RE: Determine Record Creation Date Without Audit Turned On

Thank you to all that provided feedback and suggestions. Although
there's not much that can be done with regard to the initial question,
the insight and knowledge provided is most valuable.

 

From: Dennis Williams [mailto:oracledba.williams@xxxxxxxxx] 
Sent: Friday, June 20, 2008 1:10 PM
To: david@xxxxxxxxxxxxxxxxxxxx
Cc: Langston, Chris; Jared Still; oracle-l
Subject: 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@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

 

Other related posts: