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

"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message news:<frOdnSz5I9yn3OTcRVn-pg_at_comcast.com>...
> "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

Yep my mistake - I read the OP as "when was row changed" as opposed to initial creation time. Often the two are synonymous, but it was wrong of me to assume that

Cheers
Connor Received on Mon Oct 25 2004 - 11:35:05 CDT