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

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Mon, 25 Oct 2004 13:20:51 -0400
Message-ID: <0ZidnTxps7eIq-DcRVn-tw@comcast.com>

"Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message news:910fde4f.0410250835.3eca83f8_at_posting.google.com... | "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

happens to me all the time ... but the value returned does appear to be a block-level number as well

++ mcs Received on Mon Oct 25 2004 - 12:20:51 CDT