RE: Determine Record Creation Date Without Audit Turned On

From: Langston, Chris <Chris.Langston_at_aa.com>
Date: Fri, 20 Jun 2008 17:13:38 -0500
Message-ID: <C3FFB78838AFB941929446B885F762C0033044EC@cdcdc0043002.corpaa.aa.com>

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_at_gmail.com] Sent: Friday, June 20, 2008 1:10 PM
To: david_at_databasesecurity.com
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_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

Received on Fri Jun 20 2008 - 17:13:38 CDT