RE: Determine Record Creation Date Without Audit Turned On (RESOVED)

  • From: "Langston, Chris" <Chris.Langston@xxxxxx>
  • To: "Daniel Fink" <daniel.fink@xxxxxxxxxxxxxx>, "Tao Wang" <twang@xxxxxxxx>, "Ghassan Salem" <salem.ghassan@xxxxxxxxx>
  • Date: Wed, 18 Jun 2008 15:24:07 -0500

Thank you to Daniel, Tao, Ghassan and Mark for your feedback. 

I wasn't clear in my original post but the data in question does goes
back a few years so, although I gave the suggestions due diligence, in
the end, it looks like Daniel is correct and that it's not possible.
I've let the user know that it cannot be done and they understand.

Thanks again for your input.

Regards,
Chris Langston

-----Original Message-----
From: Daniel Fink 
Sent: Wednesday, June 18, 2008 10:52 AM
To: Langston, Chris
Cc: oracle-l
Subject: Re: Determine Record Creation Date Without Audit Turned On

Short answer - not from the data dictionary.

Long answer - without specific auditing or timestamps as part of the 
row, you cannot query the database to locate these records. However, you

might be able to use logminer to locate the insert statements IF the 
redo log information is still available. I have not heard much about log

miner in the last few years, so I'm not sure what is available with 10g.

Are there any transaction logs from the application that would be of
help?


-- 
Daniel Fink

Oracle Performance, Diagnosis, Data Recovery and Training

OptimalDBA    http://www.optimaldba.com
Oracle Blog   http://optimaldba.blogspot.com

Lost Data?    http://www.ora600.nl/introduction.htm



-----Original Message-----
From: Tao Wang 
Sent: Wednesday, June 18, 2008 11:34 AM
To: Langston, Chris; oracle-l
Subject: RE: Determine Record Creation Date Without Audit Turned On

You can try the pseudo column ora_rowscn and function
scn_to_timestamp(ora_rowscn). But the function scn_to_timestamp seems to
work for rows updated relatively recently.


From: Ghassan Salem 
Sent: Wednesday, June 18, 2008 11:48 AM
To: Langston, Chris
Subject: Re: Determine Record Creation Date Without Audit Turned On

Maybe using the flashback query can get you there, if your undo allows



-----Original Message-----
From: Mark W. Farnham 
Sent: Wednesday, June 18, 2008 2:02 PM
To: Langston, Chris; 'oracle-l'
Subject: RE: Determine Record Creation Date Without Audit Turned On

Dan, Patrick, and Tao have made useful suggestions. If none of those
work
out for you and you are within range of the time in question, you could
do a
set of flashback queries into a table containing just the table in
questions's PK (not as the PK) and current query's as of time. Then if a
given PK (original table) first appears in the time window to be deleted
you're set.

You might start with a broad grain of time, then use a very fine grain
for
just the candidates. The actual data would determine how fine a grain
you
would need to absolutely identify a row as being created within some
window
of time.

Good luck!

mwf

Langston, Chris 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. 
>
> The primary DBA for the application is on vacation for the rest of the
> month and I don't know that much about the application.
>
> Any direction or source of documentation that would explain this will
be
> much appreciated. 
>
> Chris Langston
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>   



--
//www.freelists.org/webpage/oracle-l


Other related posts:

  • » RE: Determine Record Creation Date Without Audit Turned On (RESOVED)