0

I have a read-only Oracle Spatial dB. Data is imported each hour through a FME workflow. No column for the timestamp of the import has been created.

I am wondering if Oracle stores this somewhere internally anyway and whether there is some function allowing to query this data ?

Lay
  • 249
  • 1
  • 3
  • 15
  • [Possible duplicate](https://stackoverflow.com/q/43003024/266304), but are you looking for recent changes, or trying to find the time for all rows (even old ones)? `ora_rowscn` can only be converted to a timestamp [for a limited period of time](https://stackoverflow.com/q/22681705/266304). Log miner might also be an option too, again for fairly recent changes. – Alex Poole Jul 04 '18 at 10:48

1 Answers1

1

Yes, it does. It stores the system clock ("system change number" / SCN) of the last change in every database block (or every row if the table was created with ROWDEPENDENCIES).

You can use it as a pseudocolumn, so just add it to a query:

SELECT id, ora_rowscn FROM my_table;

For the last x days Oracle keeps a translation table that translates SCN to real time, which you can use with the function SCN_TO_TIMESTAMP:

SELECT id, ora_rowscn, scn_to_timestamp(ora_rowscn) FROM my_table;

Details and documentation is here.

wolφi
  • 8,091
  • 2
  • 35
  • 64