Get All Table's Row Updated After A Specific Time

Background: I have an oracle table, the table doesn't have any specific column as timestamp while table creation script. This table can have millions of rows. Example: Employee {E

Solution 1:

Oracle has ORA_ROWSCN Pseudocolumn. This columns returns "the conservative upper bound system change number (SCN)" of last transaction made on row or data block. This is a good estimate for when the block or row was last changed.

If your table is create with ROWDEPENDENCIES, ORA_ROWSCN returns scn for row. NOROWDEPENDENCIES is the default, in which case Oracle tracks SCN at the block level.

SCN_TO_TIMESTAMP allows you to converto scn to timestamp but for old scn it raises exception.

Solution 2:

Approximate update time can be retrieved with SCN_TO_TIMESTAMP(ORA_ROWSCN)

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.

https://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns007.htm