Can I get an idea when a row is created (ORA_ROWSCN Pseudocolumn)

To know the exact time of when a row is inserted or updated or deleted you have to use audit system or use logminer utility. But to use these two tool you have to configure your environment previously. However if you don't enable either audit option or supplemental logging (which is required for analysis in logminer) you can still get a rough idea about a row in a table about when it is modified/inserted more precisely the most recent change of a row.

This is possible by using ORA_ROWSCN psedocolumn.

•ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change for each row.

•The resultant value of ORA_ROWSCN is not absolutely precise, because Oracle tracks SCNs by transaction committed for the block in which the row resides.

•But a better result of the row chaging time of a table can by found by creating tables with row-level dependency tracking. This is done by CREATE TABLE ... ROWDEPENDENCIES statement. If you create your table with this statement then you can get more precise result.

•ORA_ROWSCN can't be used while querying view.

•It is not supported for external tables and also not supported for Flashback Query..

Example:
----------------
The get a rough idea of the rows of table a when the rows were created as well as SCN query as,
SQL> select scn_to_timestamp(ORA_ROWSCN), ORA_ROWSCN , a from a;

SCN_TO_TIMESTAMP(ORA_ROWSCN) ORA_ROWSCN A
--------------------------------------------------------------------------- ---------- ----------
09-JUL-08 06.53.59.000000000 AM 9020925 5
13-JUL-08 06.40.33.000000000 AM 9791113 1
13-JUL-08 06.40.33.000000000 AM 9791113 9

Related Documents:
-----------------------------
Pseudocolumns in Oracle