Re: Is there a way to find a timestamp of a row

"Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message news:417913C2.464E_at_yahoo.com...
| Prem K Mehrotra wrote:
| >
| > When I look in any of my tabls in Oracle database, I can get rowid for
| > each
| > row, but is there a way to find the time when that row was created. I
| > don't want to store cretaion time of a row as a column in my table.
| > Does Oracle internally keep this information some where, if yes how to
| > access this time information?
| >
| > Thanks,
| > Prem
|
| ora_rowscn in v10 gives the scn which can be mapped relatively closely
| to a time of day.
|
| hth
| connor
|

that doesn't seem to do it, connor -- did a quick test on 10.1.0.2.0 by updating 3 rows in the EMP table, and the value of ora_rowscn changed for every row in the table

checked the docs and the say:

For each row, ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change to the row. This pseudocolumn is useful
for determining approximately when a row was last updated. It is not absolutely
precise, because Oracle tracks SCNs by transaction committed for the block in
which the row resides. You can obtain a more fine-grained approximation of the
SCN by creating your tables with row-level dependency tracking. Please refer to
CREATE TABLE ... NOROWDEPENDENCIES | ROWDEPENDENCIES on page 16-58 for more information on row-level dependency tracking.

so, it looks like this is at best an approximation and not a good indication of row update time (and not row creation time, once the row has been updated)

++ mcs Received on Fri Oct 22 2004 - 12:19:25 CDT