RE: Determine Record Creation Date Without Audit Turned On
- From: "Langston, Chris" <Chris.Langston@xxxxxx>
- To: "Dennis Williams" <oracledba.williams@xxxxxxxxx>, <david@xxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 20 Jun 2008 17:13:38 -0500
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:
